mysql - 合并两个表并按 OrderBy 计数

Question

我想合并查询的两个结果以获得按计数排序的排序表

我有两张表第一个是:like_spotted查询是

SELECT l.id as like_id, l.spotted_id as spotted_id,count(l.spotted_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_spotted l
LEFT JOIN prof_foto f
ON f.id = l.spotted_id
LEFT JOIN sn_profilo p
ON f.profilo_id = p.id
WHERE p.id = 3 
GROUP BY l.spotted_id
ORDER BY numero_likes DESC, created DESC
LIMIT 0,5

结果:

like_id spotted_id  numero_likes    created
    31  223              6          2013-11-21 16:31:20
    11  175              5          2013-11-21 17:00:35
    23  217              2          2013-11-15 22:52:41

第二个是:like_answer查询是

SELECT l.id as like_id, l.risposta_id as risposta_id,count(l.risposta_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_risposta l
LEFT JOIN sn_risposte r
ON r.id = l.risposta_id
LEFT JOIN sn_profilo p
ON r.profilo_id = p.id
WHERE p.id = 3
GROUP BY l.risposta_id
ORDER BY numero_likes DESC, created DESC
LIMIT 0,5

like_id         risposta_id      numero_likes        created
18                  94                4           2013-11-21 17:00:35
10                  93                2           2013-11-20 13:30:53
7                   85                1           2013-11-14 12:16:11
6                   84                1           2013-11-14 12:15:38

我想要按“numero_likes”DESC 排序的“混合结果”我可以这样做吗？

Answer 1

是的，您可以使用UNION ALL来做到这一点

(SELECT l.id as like_id, l.spotted_id as spotted_id,null AS risposta_id , count(l.spotted_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_spotted l
LEFT JOIN prof_foto f
ON f.id = l.spotted_id
LEFT JOIN sn_profilo p
ON f.profilo_id = p.id
WHERE p.id = 3 
GROUP BY l.spotted_id
LIMIT 0,5)

UNION ALL
(SELECT l.id as like_id, null AS spotted_id,l.risposta_id as risposta_id,count(l.risposta_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_risposta l
LEFT JOIN sn_risposte r
ON r.id = l.risposta_id
LEFT JOIN sn_profilo p
ON r.profilo_id = p.id
WHERE p.id = 3
GROUP BY l.risposta_id
LIMIT 0,5)
ORDER BY numero_likes DESC, created DESC

UNION