combine mysql count(*)

brccelvz 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(252)

我有两个表存储来自两个不同用户的注解

Table1

id_table1 | comment | id_user | id_post

Table2

id_table2 | comment | id_someOtherUser | id_post

我真的很想让这两个表中只有一个，因为评论发布在同一个职位，但我不能，因为我有两个不同的id的两种不同类型的用户。
我的问题是，我如何才能列出所有的职位desc数量的评论结合从两个表？
如果我做了这样的事

SELECT P.*, count(*) as count from Table1 AS T1
LEFT JOIN post AS P ON T1.id_post = P.id_post GROUP BY P.id_post ORDER BY count DESC

然后我有表1的帖子，表2也可以这样做，但我如何才能将两个表中的评论合并到同一个帖子中呢？

mysql count select

来源：https://stackoverflow.com/questions/48174568/combine-mysql-count-for-two-different-tables-same-column

2条答案

按热度按时间

pw136qt21#

解决此问题的一种方法是对每个表进行单独的计数，然后进行完全的外部联接，并取每个计数的总和： SELECT id_post, (count1 + count2) AS total_count FROM (SELECT id_post, count(*) as count1 from Table1 AS T1 FULL OUTER JOIN (SELECT id_post, count(*) as count2 from Table2 AS T2) USING(id_post)) ORDER BY total_count DESC

赞(0）回复(0）举报 2021-06-25

nfeuvbwi2#

我会用 UNION ALL 要以通用格式组合这两个注解表，请执行 JOIN :

SELECT P.*, TC.count
FROM (
    SELECT Ts.id_post, count(*) AS count
    FROM (
          SELECT id_post FROM Table1
          UNION ALL
          SELECT id_post FROM Table2
    ) AS Ts
    GROUP BY Ts.id_post
) AS TC
LEFT JOIN post AS P ON TC.id_post = P.id_post
GROUP BY P.id_post
ORDER BY TC.count DESC

赞(0）回复(0）举报 2021-06-25

我来回答

combine mysql count(*)

2条答案

相关问题

热门标签

最新问答