1、如下有一张订单信息表,订单中有多个商品,写一个查询语句,得到订单信息表中同时包含有商品A、商品B、商品C的所有订单号。
表order_info:
对于上述例子数据,结果为:
解题思路:
(1)筛选出商品为(A、B、C)其中一个的订单号
(2)将结果(1)按照订单号进行分组,筛选出记录数大于或等于3条的订单
参考实现:
SELECT order_id
FROM order_info
WHERE goods in ('A','B','C')
GROUP BY order_id
HAVING COUNT(order_id) >= 3
2、在 微信 或者 QQ这样的社交应用中,人们经常会发好友申请也会收到其他人的好友申请。 表 request_info 存储了所有好友申请通过的数据记录,其中, requester_id 和 accepter_id 都是用户的编号。 写一个查询语句,求出谁拥有最多的好友和他拥有的好友数目。
表request_info :
注意:保证拥有最多好友数目的只有 1 个人。好友申请只会被接受一次,所以不会有 requester_id 和 accepter_id 值都相同的重复记录。 解释:编号为 ‘3’ 的人是编号为 ‘1’,‘2’ 和 ‘4’ 的好友,所以他总共有 3 个好友,比其他人都多。
对于上述例子数据,结果为:
解题思路:
(1)以requester_id为分组,算出每个人申请好友的数量
(2)以accepter_id为分组,算出每个人接受好友的数量
(3)全联结(1)和(2),将每个人申请好友数量和接受好友数量进行相加,得到总好友数
(4)将总好友进行倒叙排序,得到总好友数最多的一个人以及他的总好友数。
参考实现:
SELECT
TOP 1
a.user_id,
(ISNULL(a.request_num , 0) + ISNULL(b.accepter_num, 0)) as total_num
FROM
(SELECT requester_id as user_id,
COUNT(requester_id) as request_num
FROM request_info
GROUP BY requester_id) a
FULL JOIN
(SELECT accepter_id as user_id,
COUNT(accepter_id) as accepter_num
FROM request_info
GROUP BY accepter_id) b