当前位置:Gxlcms > 数据库问题 > [SQL]597(表2行数/表1行数)+602(表的上下拼接)

[SQL]597(表2行数/表1行数)+602(表的上下拼接)

时间:2021-07-01 10:21:17 帮助过:29人阅读

597. 好友申请 I :总体通过率

技术图片

技术图片

思路:

  1. 统计申请表中的不重复行数,记为表A
(SELECT COUNT(*)
FROM (SELECT DISTINCT sender_id, send_to_id
      FROM friend_request) A)
  1. 统计接受表中的不重复行数,记为表B
(SELECT COUNT(*)
FROM (SELECT DISTINCT requester_id, accepter_id
      FROM request_accepted) B)
  1. 表B结果/表A结果,IFNULL(,0)表示为空输出0

最终代码

SELECT ROUND(IFNULL(
(SELECT COUNT(*)
FROM (SELECT DISTINCT requester_id, accepter_id
      FROM request_accepted) B)
/
(SELECT COUNT(*)
FROM (SELECT DISTINCT sender_id, send_to_id
      FROM friend_request) A)
,0) ,2) AS accept_rate;

602. 好友申请 II :谁有最多的好友

技术图片

思路:

采用UNION ALL将表的requester_id和accepter_id列上下拼接,并创建临时表A

用COUNT()统计每个requester_id出现的次数

将A按照requester_id降序排列

取第一行。

SELECT A.requester_id AS id, COUNT(A.requester_id) AS num
FROM(
SELECT requester_id
FROM request_accepted
UNION ALL
SELECT accepter_id
FROM request_accepted) A 
GROUP BY A.requester_id
ORDER BY num DESC
LIMIT 1;

[SQL]597(表2行数/表1行数)+602(表的上下拼接)

标签:union   排列   mysql   com   mys   des   class   临时表   select   

人气教程排行