题目描述:
RequestAccepted
表:
+----------------+---------+ | Column Name | Type | +----------------+---------+ | requester_id | int | | accepter_id | int | | accept_date | date | +----------------+---------+ (requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。 这张表包含发送好友请求的人的 ID ,接收好友请求的人的 ID ,以及好友请求通过的日期。
编写解决方案,找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
查询结果格式如下例所示。
示例 1:
输入: RequestAccepted 表: +--------------+-------------+-------------+ | requester_id | accepter_id | accept_date | +--------------+-------------+-------------+ | 1 | 2 | 2016/06/03 | | 1 | 3 | 2016/06/08 | | 2 | 3 | 2016/06/08 | | 3 | 4 | 2016/06/09 | +--------------+-------------+-------------+ 输出: +----+-----+ | id | num | +----+-----+ | 3 | 3 | +----+-----+ 解释: 编号为 3 的人是编号为 1 ,2 和 4 的人的好友,所以他总共有 3 个好友,比其他人都多。
题解:
第一种:分两张表分别筛选出发送id和相应次数,接收id和相应次数。之后再全外连接可以保留所有带null的数据。之后选出最大的id和次数。
/* Write your T-SQL query statement below */
select
--top 与 order by 选出最大次数的id,isnull判断前一个值是否为空,空就为第二个参数值。
top 1 isnull(requester_id, accepter_id) id, isnull(num1, 0) + isnull(num2, 0) as num
from
--所用两张表逻辑相同,统计发送id和统计发送或接收次数,并单独成表。
(select
requester_id, count(requester_id) as num1
from
RequestAccepted
group by
requester_id) t1
full outer join
(select
accepter_id, count(accepter_id) as num2
from
RequestAccepted
group by
accepter_id) t2
on
--连接时也有null
t1.requester_id = t2.accepter_id
order by
num desc
第二种:与第一种类似,但是通过union all进行分组。
select
--top和order by联用选出最大次数,统计次数相加使用sum
top 1 id, sum(num) num
from
--与第一种方式类似的先选出发送和接收id和他们各自的次数,再使用union all相加表。
(select
requester_id id, count(requester_id) as num
from
RequestAccepted
group by
requester_id
union all
select
accepter_id id, count(accepter_id) as num
from
RequestAccepted
group by
accepter_id) t
--对表进行分组通过id字段。
group by
id
order by
num desc