0.知识补充

• begin end

DECLARE @a varchar(50)
SET @a = `sql开发`
begin
    select @a
end

• if else

DECLARE @a varchar(50)
SET @a = `sql开发`
if len(@a) >5
    select @a
else 
    select `less than 5`

1.查询既学过01也学过03的课程的学生ID

--Method 1 笛卡尔积
select * from F0215 a, F0215 b where a.StuID = b.StuID and a.CID =1 and b.CID = 3

--Method 2 子查询
select * from (SELECT * FROM F0215 WHERE CID =1) A1
INNER JOIN 
( SELECT * FROM F0215 WHERE CID =3)A2
ON A1.StuID = A2.StuID

--Method3 分组
SELECT StuID FROM F0215 
WHERE CID IN (1,3)
GROUP BY StuID
HAVING COUNT(StuID)=2

--Method 4 交集
SELECT SC.STUID FROM F0215 SC
WHERE  SC.CID=1
INTERSECT
SELECT SC.STUID FROM F0215 SC
WHERE  SC.CID=3

2.取price最大的一行的所有数据

--方法1
SELECT a.* FROM F0217 a
JOIN
(
SELECT Uname,Max(Price) Price FROM F0217
GROUP BY Uname 
) b ON a.Uname=b.Uname AND a.Price=b.Price

--方法2
with new_price as(
SELECT *,ROW_NUMBER() OVER(partition by Uname order by Price desc) num FROM F0217  
) 
select * from new_price where num =1

--方法3
SELECT * 
FROM   
 ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY Uname ORDER BY Price DESC ) AS num
   FROM F0217
 )  t
WHERE num = 1

注意:方法2和方法3本质上是一样的

统计胜利队伍和失败的队伍

image.png
思路：（1）看到字符串的统计，或者字符串转为数字，首先想到的就是case when
（2）由于Team1和Team2，并不是相同的，所以需要使用union all用来统计所有

select 
    Team_1, COUNT(1) as matches_played, SUM(score) as matches_won, COUNT(1) - SUM(score) as matches_not_won
from (
    select 
        Team_1, case when Winner = Team_1 then 1 else 0 end as score
    from icc_world_cup
    union all
    select 
        Team_2, case when Winner = Team_2 then 1 else 0 end as score
    from icc_world_cup) as all_team
    group by Team_1

找出新顾客和重复的顾客

• 统计出每日的新顾客人数和重复逛店的人数

  
  image.png

• 原表

  
  image.png
• 分析：
  （1）新顾客的天数就是根据客户最早购物的天数，所以需要找出min(order_date) 创建新的first_visited表
  (2) 在用原表join新表，在根据第一次购买的天数相等则是新用户，否则为旧用户
  （3）CTEs的final表的使用
• sql

with first_visited as(
select customer_id,MIN(order_date) as first_visited_day from customer_orders
group by customer_id),
visited_flag as
(
select b.*,
case when a.first_visited_day = b.order_date then 1 else 0 end as new_customer,
case when a.first_visited_day != b.order_date then 1 else 0 end as reapet_customer
from first_visited a
inner join customer_orders b
on a.customer_id = b.customer_id
)
select order_date,SUM(new_customer) as new_customer,SUM(reapet_customer) AS reapet_customer
from visited_flag
GROUP BY order_date

游客的访问记录和访问的设备

image.png

• 前置必会：将原表转为

  
  image.png

with a as(
select distinct name, [resources] from [entries]
)
select name, string_agg(resources,',') as resources_used from a group by name

• 思路：
  1. 为了找到最多访问的楼层，需要引入rank() 排序将最多楼层的访问次数排到第一名，这样才可以选择
  2. 对于前置resources的获取，可以使用两个cte来获得

with dis_resources as(
  select distinct name, [resources] from [entries]),
  agg_resources as (select name, string_agg(resources,',') as resources_used from dis_resources group by name),
 floor_vists as
    (
    SELECT name,floor , COUNT(email) as floor_visits,
    rank() over(partition by name order by COUNT(email) desc) as r
    FROM [entries]
    GROUP BY name,floor
 )
select 
    a.name,
    SUM(floor_visits) as total_visits, 
    most_v_floor = (select floor from floor_vists b where r =1 and a.name =b.name),ar.resources_used 
from floor_vists a
inner join agg_resources ar on a.name =ar.name
group by a.name,ar.name,ar.resources_used

找出哪些百分之20的商品提供了百分之80的销售额

 WITH pro_sales as(
 SELECT [Product_ID], SUM(Sales) AS product_sales
 FROM [SQL_EVERY_DAY].[dbo].[Orders]
 group by [Product_ID]), total_running_sales as(
select [Product_ID],product_sales,sum(product_sales) over(order by product_sales desc) as running_sales from pro_sales), cal_sales as(
select [Product_ID],product_sales,running_sales from total_running_sales where running_sales <= (select sum(Sales)*0.8 from [Orders]) )
select COUNT(1)*1.0/(select COUNT(distinct Product_ID) from [Orders]) as product_20 from cal_sales