今天又整理了一些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
No comments yet