今天又整理了一些SQL题目，
把“简单”那一章的题目过了一遍
其实也不简单。
在博客上也分享一下吧，
说不定有学SQL Server的人能看到。
—————————————————–
— 如果在自己的测试DB跑的话，可以跑一下注释的脚本
—————————————————–
/*
CREATE TABLE competition_list(
team_name NVARCHAR(8)
)
INSERT INTO
competition_list (team_name)
VALUEs (N’谁与争锋队’)
,(N’必胜队’)
,(N’乘风破浪队’)
,(N’群英汇队’)
,(N’梦之队’)
*/
–题目1：比赛名单整理
–问题：每个参赛队伍都会和其他参赛队伍开展一次组队比赛，要求输出两两参赛队伍的所有比赛情况组合
–（两者分别为队伍A和队伍B），并按队名依次升序排列
— select * from competition_list
–结果
–队伍 A 队伍 B
–乘风破浪队 必胜队
–乘风破浪队 梦之队
–乘风破浪队 群英汇队
–乘风破浪队 谁与争锋队
–必胜队 梦之队
–必胜队 群英汇队
–必胜队 谁与争锋队
–梦之队 群英汇队
–梦之队 谁与争锋队
–群英汇队 谁与争锋队
SELECT a.team_name AS ‘队伍 A’
,b.team_name AS ‘队伍 B’
FROM competition_list a
INNER JOIN competition_list b
ON a.team_name < b.team_name
ORDER BY a.team_name, b.team_name;
/*
CREATE TABLE product_promotion(
commodity_id VARCHAR(8),
start_date DATE,
end_date DATE
)
INSERT INTO
product_promotion (commodity_id,start_date,end_date)
VALUES (‘a001′,’2021-01-01′,’2021-01-06’)
,(‘a002′,’2021-01-01′,’2021-01-10’)
,(‘a003′,’2021-01-02′,’2021-01-07’)
,(‘a004′,’2021-01-05′,’2021-01-07’)
,(‘b001′,’2021-01-05′,’2021-01-10’)
,(‘b002′,’2021-01-04′,’2021-01-06’)
,(‘c001′,’2021-01-06′,’2021-01-08’)
,(‘c002′,’2021-01-02′,’2021-01-04’)
,(‘c003′,’2021-01-08′,’2021-01-15′);
*/
–题目2：参与优惠活动的商品
–commodity_id商品ID，start_date商品优惠活动开始日期，end_date商品优惠活动结束日期
–问题：查询在2021年1月7日至2021年1月9日期间参与优惠活动的商品
–select * from product_promotion
–结果
–commodity_id
–a002
–a003
–a004
–b001
–c001
–c003
select commodity_id
from product_promotion
where (start_date<=’2021-1-7′ and end_date>=’2021-1-9′)
or(end_date>=’2021-1-7′ and end_date<=’2021-1-9′)
or(start_date>=’2021-1-7′ and start_date<=’2021-1-9′)
or(start_date>=’2021-1-7′ and end_date<=’2021-1-9′)
/*
CREATE TABLE sold_succession(
order_id INT,
commodity_id VARCHAR(8)
)
INSERT INTO
sold_succession
(order_id,commodity_id)
VALUES (1,’c_001′)
,(2,’c_001′)
,(3,’c_002′)
,(4,’c_002′)
,(5,’c_002′)
,(6,’c_001′)
,(7,’c_003′)
,(8,’c_003′)
,(9,’c_003′)
,(10,’c_003′)
,(11,’c_001′);
*/
–题目3：连续售出的商品
–问题找出连续下单大于或等于3次的商品ID
–select * from sold_succession
–结果
–commodity_id
–c_002
–c_003
SELECT commodity_id
,order_id
,LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY order_id) AS temp
FROM sold_succession
order by order_id
SELECT DISTINCT commodity_id
FROM
(
SELECT commodity_id
,order_id
,LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY
order_id) AS temp
FROM sold_succession
)a
WHERE order_id = temp + 2;
/*
CREATE TABLE student_info(
student_id INT,
student_name NVARCHAR(8)
)
INSERT INTO
student_info (student_id,student_name)
VALUEs (1,N’李明’)
,(2,N’王猛’)
,(3,N’吴丽丽’)
,(4,N’张飞’)
,(5,N’赵涛’);
*/
–题目4：奇偶互换位置
–问题：将奇数学号和偶数学号的相邻学生调换学号。
–若最后一个是奇数学号，则该学号不参与调换，最终结果按照最新学号升序排列
–select * from student_info
–结果
–student_id student_name
–1 王猛
–2 李明
–3 张飞
–4 吴丽丽
–5 赵涛
select case when student_id%2=1 and student_id=(select count(*) from student_info) then student_id
when student_id%2=1 then student_id+1
when student_id%2=0 then student_id-1 end as student_id,
student_name
from student_info
order by student_id
/*
CREATE TABLE game_ranking(
game VARCHAR(8),
category VARCHAR(16),
downloads INT
)
INSERT INTO
game_ranking (game,category,downloads)
VALUES (‘A’,’puzzle’,13628)
,(‘B’,’shooting’,2830)
,(‘C’,’shooting’,1920)
,(‘D’,’action’,23800)
,(‘E’,’puzzle’,842)
,(‘F’,’shooting’,48201)
,(‘G’,’action’,4532)
,(‘H’,’puzzle’,1028)
,(‘I’,’action’,48910)
,(‘J’,’shooting’,342)
,(‘K’,’puzzle’,32456)
,(‘L’,’action’,2801)
,(‘M’,’puzzle’,1248)
,(‘N’,’action’,8756);
*/
–题目5：热门游戏排行
–问题：查询每个类别下载量排在前两名的游戏
–select * from game_ranking
–结果
–category name
–action I,D
–puzzle K,A
–shooting F,B
;with CTE
as(
select game,category from
(
select game,category, rank() over(partition by category order by downloads desc) as rankid
from game_ranking
) a
where a.rankid<=2
)
SELECT B.category ,
LEFT(tempname, LEN(tempname) – 1) AS name
FROM ( SELECT category ,
( SELECT game + ‘,’
FROM CTE
WHERE category = A.category
FOR
XML PATH(”)
) AS tempname
FROM CTE A
GROUP BY category
) B
/*
CREATE TABLE comparative_analysis(
[month] VARCHAR(8),
sales_volume INT
)
INSERT INTO
comparative_analysis ([month],sales_volume)
VALUES (‘2020-05’,834)
,(‘2020-06’,604)
,(‘2020-07’,715)
,(‘2020-08’,984)
,(‘2020-09’,1024)
,(‘2020-10’,893)
,(‘2020-11’,485)
,(‘2020-12’,890)
,(‘2021-01’,563)
,(‘2021-02’,301)
,(‘2021-03’,1145)
,(‘2021-04’,1804)
,(‘2021-05′,1493);
*/
–题目6：商品销量同环比
–问题：统计2021年5月销量的同环比情况
–输出内容包括：sales_volume销量，year_ratio同比销量，month_ratio环比销量
–select * from comparative_analysis
–结果
–sales_volume year_ratio month_ratio
–1493 1.790167865707 0.827605321507
select sales_volume,1.0*sales_volume/year_on_year as year_ratio, 1.0*sales_volume/month_on_month as month_ratio
from
(select [month],sales_volume,
lag(sales_volume,1) over(order by [month]) as month_on_month,
lag(sales_volume,12) over(order by [month]) as year_on_year
from comparative_analysis
) a
where [month]=’2021-05’
/*
CREATE TABLE fresh_food(
userid VARCHAR(8),
app VARCHAR(30)
)
INSERT INTO
fresh_food (userid,app)
VALUES (‘u001′,’A,B’)
,(‘u002′,’C,D,A’)
,(‘u003′,’E’)
,(‘u004′,’A’)
,(‘u005′,’F,D’)
,(‘u006′,’E,G’)
,(‘u007′,’C,B’)
,(‘u008′,’H,J’)
,(‘u009′,’J’)
,(‘u010′,’A,K,E’);
*/
–题目7：社区生鲜App覆盖分析
–问题：查询安装了“A”App的用户人数
–SELECT * FROM fresh_food;
–结果
–Qty
–4
select count(*) as Qty
from fresh_food
where app like ‘%A%’
/*
CREATE TABLE group_buy(
user_id VARCHAR(8),
login_source VARCHAR(15),
login_date DATE,
order_count INT
)
INSERT INTO
group_buy (user_id,login_source,login_date,order_count)
VALUES (‘a001′,’applet’,’2021-03-20′,1)
,(‘a002′,’application’,’2021-03-20′,0)
,(‘a003′,’web’,’2021-03-21′,0)
,(‘a002′,’application’,’2021-03-21′,2)
,(‘a001′,’applet’,’2021-03-21′,4)
,(‘a003′,’application’,’2021-03-22′,1)
,(‘a001′,’applet’,’2021-03-22′,1)
,(‘a004′,’application’,’2021-03-23′,1);
*/
–题目8：社区团购行为分析
–问题一：查询每个用户首次登陆的渠道名称
–问题二：查询用户登陆日期和累计下单数量
–select * from group_buy
–结果一
–user_id login_source
–a001 applet
–a002 application
–a003 web
–a004 application
–结果二
–user_id login_date total_order_count
–a001 2021-03-20 1
–a001 2021-03-21 5
–a001 2021-03-22 6
–a002 2021-03-20 0
–a002 2021-03-21 2
–a003 2021-03-21 0
–a003 2021-03-22 1
–a004 2021-03-23 1
select a.user_id,a.login_source from group_buy a,
(
select user_id,min(login_date) as login_date from group_buy
group by user_id
) b
where a.user_id=b.user_id and a.login_date=b.login_date
select user_id,login_date,sum(order_count) over(partition by user_id order by login_date rows between unbounded preceding and current row) total_order_count
from group_buy
order by user_id,login_date
SELECT user_id
,login_date
,SUM(order_count) OVER (PARTITION BY user_id ORDER BY login_date) total_order_count
FROM group_buy
order by user_id,login_date
/*
CREATE TABLE gmv_info(
date DATE,
mall_gmv INT
)
INSERT INTO
gmv_info (date,mall_gmv)
VALUES (‘2020-11-04’,12325)
,(‘2020-11-05’,15497)
,(‘2020-11-06’,13216)
,(‘2020-11-07’,16548)
,(‘2020-11-08’,17367)
,(‘2020-11-09’,20124)
,(‘2020-11-10’,37325)
,(‘2020-11-11’,134367)
,(‘2020-11-12’,54331)
,(‘2020-11-13’,22212)
,(‘2020-11-14’,16312)
,(‘2020-11-15’,14384)
,(‘2020-11-16’,12314)
,(‘2020-11-17’,13146);
*/
–题目9：“双11”活动的电商GMV分析
–问题：查询2020年11月11日起的一周时间内，相比7天前GMV的变化率
— select * from gmv_info
–结果
–date ratio
–2020-11-11 990.198782961460%
–2020-11-12 250.590436858746%
–2020-11-13 68.069007263922%
–2020-11-14 -1.426154218032%
–2020-11-15 -17.176253814706%
–2020-11-16 -38.809381832637%
–2020-11-17 -64.779638312123%
;with CTE
as(
SELECT date
,mall_gmv
,(mall_gmv-LAG(mall_gmv,7) OVER (ORDER BY date )) AS gmv_diff
,100.0*(mall_gmv-LAG(mall_gmv,7) OVER (ORDER BY date ))
/ LAG(mall_gmv,7) OVER (ORDER BY date ) AS gmv_rate
FROM gmv_info
WHERE date BETWEEN ‘2020-11-04’ AND ‘2020-11-17′
)
SELECT date
,CONCAT(gmv_rate,’%’) AS ratio
FROM CTE
WHERE date BETWEEN ‘2020-11-11’ AND ‘2020-11-17’;
/*
CREATE TABLE original_text(
text_id VARCHAR(8),
text_content VARCHAR(16)
)
INSERT INTO
original_text (text_id,text_content)
VALUES (‘t001′,’!**@%&’)
,(‘t002′,’*’)
,(‘t003′,’@@!***&*’)
,(‘t004′,’%&*$@’)
,(‘t005′,’*******’);
*/
–题目10：统计字符出现次数
–问题：统计每条文本中符号“*”出现的次数
–select * from original_text
–结果
–text_id num
–t001 2
–t002 1
–t003 4
–t004 1
–t005 7
SELECT text_id
,LEN(text_content) – LEN(REPLACE(text_content,’*’,”)) AS num
FROM original_text;
/*
CREATE TABLE website_visit(
data_content VARCHAR(15)
)
INSERT INTO
website_visit
(data_content)
VALUES (‘201812011241’)
,(‘201812022493’)
,(‘201812030845’)
,(‘201812041230’)
,(‘201912012317’)
,(‘201912022520’)
,(‘201912031945’)
,(‘201912042031’)
,(‘202012013015’)
,(‘202012022914’)
,(‘202012032319’)
,(‘202012043143’);
*/
–题目11：网站访问量分析
–字段前8位日期后4位访问量
–问题：查询每年单天最大网站访问量
–select * from website_visit
–结果
–Year MaxVisit
–2018 2493
–2019 2520
–2020 3143
select substring(data_content,1,4) as Year,max(substring(data_content,9,4)) as MaxVisit
from website_visit
group by SUBSTRING(data_content,1,4)
/*
CREATE TABLE convert_table(
text_id VARCHAR(8),
text_content VARCHAR(8)
)
INSERT INTO
convert_table (text_id,text_content)
VALUES (‘t001′,’a’)
,(‘t002′,’b’)
,(‘t001′,’c’)
,(‘t002′,’d’)
,(‘t002′,’e’)
,(‘t003′,’f’);
*/
–题目12：文本记录连接
–问题：将相同的text_id的text_content部分通过,符号拼接
–select * from convert_table
–结果
–text_id text_content
–t001 a,c
–t002 b,d,e
–t003 f
SELECT B.text_id ,
LEFT(tempname, LEN(tempname) – 1) AS text_content
FROM ( SELECT text_id ,
( SELECT text_content + ‘,’
FROM convert_table
WHERE text_id = A.text_id
FOR
XML PATH(”)
) AS tempname
FROM convert_table A
GROUP BY text_id
) B
/*
CREATE TABLE purchase_quantity(
year VARCHAR(8),
quarter VARCHAR(8),
amount INT
)
INSERT INTO
purchase_quantity (year,quarter,amount)
VALUES (‘2019′,’1’,1200)
,(‘2019′,’2’,1000)
,(‘2019′,’3’,800)
,(‘2019′,’4’,1300)
,(‘2020′,’1’,1100)
,(‘2020′,’2’,950)
,(‘2020′,’3’,700)
,(‘2020′,’4’,1050);
*/
–题目13：行列互换
–问题：将表结构转换为year 一季度 二季度 三季度 四季度
–select *from purchase_quantity
–结果
–year 一季度 二季度 三季度 四季度
–2019 1200 1000 800 1300
–2020 1100 950 700 1050
SELECT year
,SUM(CASE WHEN quarter = 1
THEN amount
ELSE 0
END) AS ‘一季度’
,SUM(CASE WHEN quarter = 2
THEN amount
ELSE 0 END)
AS ‘二季度’
,SUM(CASE WHEN quarter = 3
THEN amount
ELSE 0
END) AS ‘三季度’
,SUM(CASE WHEN quarter = 4
THEN amount
ELSE 0
END) AS ‘四季度’
FROM purchase_quantity
GROUP BY year;
/*
CREATE TABLE consumer_order(
order_id VARCHAR(8),
money INT
)
INSERT INTO
consumer_order (order_id,money)
VALUES (‘a001′,’2000’)
,(‘a002’,4000)
,(‘a003’,6000)
,(‘a004’,2000)
,(‘a005’,4000)
,(‘a006’,3000)
,(‘a007’,2000)
,(‘a008’,4000)
,(‘a009’,5000);
*/
–题目14：寻找符合要求的订单
–问题：按订单ID的顺序依次累加money，获取累计值与20000相差最小时对于的订单ID和最小差值
–select *from consumer_order
–结果
–order_id diff
–a006 1000
SELECT top 1 order_id
,ABS(20000-SUM(money) OVER (ORDER BY order_id)) AS diff
FROM consumer_order
ORDER BY diff,order_id;
/*
CREATE TABLE user_register_info(
user_id VARCHAR(8),
register_date DATE
)
INSERT INTO
user_register_info (user_id,register_date)
VALUES (‘a001′,’2020-10-15’)
,(‘a002′,’2020-11-20’)
,(‘a003′,’2020-12-13’)
,(‘a004′,’2021-01-18’);
CREATE TABLE user_order_info(
user_id VARCHAR(8),
order_id VARCHAR(8),
order_date DATE,
commodity_id VARCHAR(8)
)
INSERT INTO
user_order_info (order_id,user_id,order_date,commodity_id)
VALUES (‘o001′,’a001′,’2020-11-12′,’c005’)
,(‘o002′,’a002′,’2020-12-27′,’c003’)
,(‘o003′,’a002′,’2021-01-12′,’c003’)
,(‘o004′,’a003′,’2021-02-25′,’c001’)
,(‘o005′,’a004′,’2021-03-12′,’c004’)
,(‘o006′,’a004′,’2021-03-14′,’c005’);
*/
–题目15：用户购物信息统计
–第一张表为注册信息表 第二张表用户订单表 订单ID，用户ID，订单日期，商品ID
–问题：查询每个用户的注册日期及其在2021年的订单总数
–select * from user_register_info
–select * from user_order_info
–结果
–user_id register_date order_2021
–a001 2020-10-15 0
–a002 2020-11-20 1
–a003 2020-12-13 1
–a004 2021-01-18 2
SELECT a.user_id
,a.register_date
,COUNT(order_id) AS order_2021
FROM user_register_info a
LEFT JOIN user_order_info b
ON a.user_id = b.user_id
and order_date like ‘2021%’
GROUP BY a.user_id
,a.register_date;
/*
CREATE TABLE user_order(
user_id VARCHAR(8),
payment INT,
paytime DATETIME
)
INSERT INTO
user_order (user_id,payment,paytime)
VALUES (‘a001′,500,’2021-02-01 13:25’)
,(‘a001′,800,’2021-02-03 09:10’)
,(‘b001′,150,’2021-02-03 15:18’)
,(‘a002′,90,’2021-02-05 08:10’)
,(‘a001′,1050,’2021-02-06 10:34’)
,(‘b001′,400,’2021-02-07 18:19’);
*/
–题目16：用户收单消费金额
–问题：查找每个用户下单时间最早的订单金额
–select * from user_order
–结果
–user_id payment
–a001 500
–a002 90
–b001 150
select b.user_id , b.payment
from
(
select a.user_id,a.payment,
rank() over(partition by user_id order by paytime ) as RankID
from user_order a
) b
where RankID=1
/*
CREATE TABLE coupon_collection(
user_id VARCHAR(8),
collection_date DATE
)
INSERT INTO
coupon_collection (user_id,collection_date)
VALUES (‘u001′,’2021-05-01’)
,(‘u002′,’2021-05-01’)
,(‘u003′,’2021-05-02’)
,(‘u004′,’2021-05-02’)
,(‘u005′,’2021-05-03’);
CREATE TABLE consumption_info(
user_id VARCHAR(8),
consumption_date DATE
)
INSERT INTO
consumption_info (user_id,consumption_date)
VALUES (‘u002′,’2021-04-28’)
,(‘u001′,’2021-04-29’)
,(‘u001′,’2021-05-03’)
,(‘u003′,’2021-05-05’)
,(‘u005′,’2021-05-06’)
,(‘u001′,’2021-05-08’)
,(‘u004′,’2021-05-09’)
,(‘u006′,’2021-05-09’)
,(‘u003′,’2021-05-10’)
,(‘u002′,’2021-05-10’);
*/
–题目17：优惠券使用分析
–表一优惠券领取日期 表二消费情况
–问题：用户领取优惠券在次日生效，并在之后的7天内购物时自动生效，使用次数不限
–要求获取成功使用优惠券消费的用户及其对应的消费次数
–select * from coupon_collection
–select * from consumption_info
–结果
–user_id num
–u001 2
–u003 1
–u004 1
–u005 1
SELECT a.user_id,
COUNT(collection_date) AS num
FROM coupon_collection a
INNER JOIN consumption_info b
ON a.user_id = b.user_id
WHERE DATEDIFF(day,collection_date,consumption_date) BETWEEN 1 AND 7
GROUP BY a.user_id;
/*
CREATE TABLE game_login(
user_id VARCHAR(8),
login_time VARCHAR(25)
)
INSERT INTO
game_login (user_id,login_time)
VALUES (‘u001′,’2021-03-01 06:01:12’)
,(‘u001′,’2021-03-01 07:14:20’)
,(‘u002′,’2021-03-01 07:20:22’)
,(‘u003′,’2021-03-01 08:22:45’)
,(‘u001′,’2021-03-01 11:10:23’)
,(‘u004′,’2021-03-01 12:00:10’)
,(‘u002′,’2021-03-01 18:03:52’)
,(‘u005′,’2021-03-01 20:10:29’)
,(‘u003′,’2021-03-01 21:11:50’);
*/
–题目18：游戏玩家登陆情况分析
–问题一：查询在一天中多次登陆游戏的玩家及其登陆的次数
–问题二：对于在一天中多次登陆游戏的玩家，值查找当天最后一条记录
–select * from game_login
–结果一
–user_id num
–u001 3
–u002 2
–u003 2
–结果二
–user_id login_time
–u001 2021-03-01 11:10:23
–u002 2021-03-01 18:03:52
–u003 2021-03-01 21:11:50
SELECT user_id ,COUNT(date) AS num
FROM
(
SELECT user_id
,SUBSTRING(login_time,1,10) AS date
FROM game_login
) a
GROUP BY user_id,date
HAVING COUNT(user_id)>1;
;with CTE
as
(
SELECT user_id ,COUNT(date) AS num
FROM
(
SELECT user_id
,SUBSTRING(login_time,1,10) AS date
FROM game_login
) a
GROUP BY user_id,date
HAVING COUNT(user_id)>1
)
select c.user_id,c.login_time
from
(
select a.user_id,a.login_time,rank() over(partition by a.user_id order by login_time desc) as RankID
from game_login a
where exists(
select b.user_id
from CTE b
where a.user_id=b.user_id)
) as c
where c.rankID =1
/*
CREATE TABLE employee_performance(
employee_id VARCHAR(8),
target_a INT,
target_b INT,
target_c INT,
target_d INT,
target_e INT
)
INSERT INTO
employee_performance (employee_id,target_a,target_b,target_c,target_d,target_e)
VALUES (‘u001’,9,7,9,10,6)
,(‘u002’,8,8,8,9,10)
,(‘u003’,10,10,10,9,9)
,(‘u004’,5,7,9,8,8)
,(‘u005’,7,7,5,4,6)
,(‘u006’,10,9,10,7,8)
,(‘u007’,8,7,8,9,6)
,(‘u008’,8,9,10,10,6)
,(‘u009’,5,5,6,7,6)
,(‘u010’,10,10,10,8,7);
*/
–题目19：员工绩效考核
–target_*:*指标得分
–问题：根据5个指标的得分情况评选优秀员工
–要求至少4个指标分数大于8分
–查询优秀员工的ID和总分，按总分和ID排序
–select * from employee_performance
–结果
–employee_id total_score
–u003 48
–u010 45
–u006 44
–u002 43
–u008 43
SELECT employee_id
,total_score
FROM
(
SELECT employee_id
,CASE WHEN target_a>=8 THEN 1 ELSE 0 END target_a
,CASE WHEN target_b>=8 THEN 1 ELSE 0 END target_b
,CASE WHEN target_c>=8 THEN 1 ELSE 0 END target_c
,CASE WHEN target_d>=8 THEN 1 ELSE 0 END target_d
,CASE WHEN target_e>=8 THEN 1 ELSE 0 END target_e
,target_a+target_b+target_c+target_d+target_e total_score
FROM employee_performance
) a
WHERE (a.target_a+a.target_b+a.target_c+a.target_d+a.target_e)>=4
ORDER BY total_score DESC,employee_id;
/*
CREATE TABLE product_sale(
product_id VARCHAR(8),
product_category VARCHAR(8),
sale INT
)
INSERT INTO
product_sale (product_id,product_category,sale)
VALUES (‘p001′,’c001’,14600)
,(‘p002′,’c001’,23300)
,(‘p003′,’c001’,8000)
,(‘p004′,’c002’,40800)
,(‘p005′,’c002’,5300)
,(‘p006′,’c003’,12900);
*/
–题目20：找出各类别商品销量最高的商品
–问题：查询不同类别商品销量最高的商品信息
–select * from product_sale
–结果
–product_category product_id sale
–c001 p002 23300
–c002 p004 40800
–c003 p006 12900
select a.product_category,a.product_id,a.sale
from
(
select product_category,product_id,sale, rank() over(partition by product_category order by sale desc) as RankID
from product_sale
) as a
where RankID=1
/*
CREATE TABLE employee(
employee_id VARCHAR(8),
employee_name VARCHAR(8),
employee_salary INT,
department VARCHAR(8)
)
INSERT INTO
employee (employee_id,employee_name,employee_salary,department)
VALUES (‘a001′,’Bob’,7000,’b1′)
,(‘a002′,’Jack’,9000,’b1′)
,(‘a003′,’Alice’,8000,’b2′)
,(‘a004′,’Ben’,5000,’b2′)
,(‘a005′,’Candy’,4000,’b2′)
,(‘a006′,’Allen’,5000,’b2′)
,(‘a007′,’Linda’,10000,’b3′);
CREATE TABLE department(
department_id VARCHAR(8),
department_name VARCHAR(8)
)
INSERT INTO
department (department_id,department_name)
VALUES (‘b1′,’Sales’)
,(‘b2′,’IT’)
,(‘b3′,’Product’);
*/
–题目21：找出每个不猛薪资第二高的员工
–select * from employee
–select * from department
–结果
–employee_id employee_name employee_salary department_name
–a001 Bob 7000 Sales
–a004 Ben 5000 IT
–a006 Allen 5000 IT
SELECT a.employee_id
,a.employee_name
,a.employee_salary
,b.department_name
FROM
(
SELECT *
,RANK() OVER (PARTITION BY department ORDER BY employee_salary DESC) AS ranking
FROM employee
) AS a
INNER JOIN department AS b
ON a.department = b.department_id
WHERE a.ranking = 2;
/*
CREATE TABLE pk_info(
request_id VARCHAR(8),
accept_id VARCHAR(8),
accept_date DATE
)
INSERT INTO
pk_info (request_id,accept_id,accept_date)
VALUES (‘a001′,’a002′,’2021-03-01’)
,(‘a001′,’a003′,’2021-03-01’)
,(‘a001′,’a004′,’2021-03-02’)
,(‘a002′,’a003′,’2021-03-02’)
,(‘a005′,’a003′,’2021-03-03’)
,(‘a006′,’a001′,’2021-03-04’)
,(‘a004′,’a003′,’2021-03-05’);
*/
–题目22：找出游戏中最活跃的用户
–表为用户对战情况表
–问题：统计对战次数最多的用户ID及其对战的次数
–select * from pk_info
–结果
–user_id cnt
–a001 4
SELECT top 1 user_id
,COUNT(*) AS cnt
FROM
(
SELECT request_id AS user_id
FROM pk_info
UNION ALL
SELECT accept_id AS user_id
FROM pk_info
)a
GROUP BY user_id
ORDER BY cnt desc
