今天又整理了一些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-9′) or(end_date>=’2021-1-7′ and end_date=’2021-1-7′ and 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.0sales_volume/year_on_year as year_ratio, 1.0sales_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,targete) 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
