mysql - 基于行数的复合查询方案

Question

我有三个表

td_idea

|------------|-------------|
| idea_id    | idea_name   |
|------------|-------------|

然后 td_idea_comment

|-----------|------------|----------
|comm_id    | idea_id    | user_id  |
|-----------|------------|----------|

和 td_idea_like

|-----------|------------|----------
|comm_id    | idea_id    | user_id  |
|-----------|------------|----------|

现在我需要使用一个查询，该查询将根据编号对想法进行升序排序。 td_idea_like 中的票数和否。对 td_idea_comment 的评论数。

三个表的例子如下

td_idea

|------------|-------------|
| idea_id    | idea_name   |
|------------|-------------|
| 1          | Pink Ruby   |
|------------|-------------|
| 2          | Black_ruby  |
|------------|-------------|

td_idea_comment

|------------|-------------|---------------|
| comm_id    | idea_id     | user_id       |
|------------|-------------|---------------|
| 1          |     1       |     1         |
|------------|-------------|---------------|
| 2          |     2       |     1         |
|------------|-------------|---------------|
| 3          |     1       |     2         |
|------------|-------------|---------------|
| 4          |     1       |     3         |
|------------|-------------|---------------|

td_idea_like

|------------|-------------|---------------|
| like_id    | idea_id     | user_id       |
|------------|-------------|---------------|
| 1          |     1       |     1         |
|------------|-------------|---------------|
| 2          |     2       |     1         |
|------------|-------------|---------------|
| 3          |     1       |     2         |
|------------|-------------|---------------|
| 4          |     1       |     3         |
|------------|-------------|---------------|

我使用了这个查询

SELECT * FROM td_idea,td_idea_comment,tyd_idea_like

WHERE td_idea.idea_id=td_idea_comment.idea_id
AND td_idea.idea_id=td_idea_like.idea_id

Order BY (SELECT COUNT(*) AS tot_comment FROM td_idea,td_idea_comment
WHERE td_idea.idea_id=td_idea_comment.idea_id),
(SELECT COUNT(*) AS tot_like FROM td_idea,td_idea_like
WHERE td_idea.idea_id=td_idea_like.idea_id)

但如果在任何情况下 td_idea_comment 中没有基于特定想法的行或 td_idea_like 中没有基于特定想法的行，它会返回零。

说:

• idea id 1 有 3 个赞和 3 个评论
• idea id 2 有 1 个赞 2 个评论
• idea id 3 有 0 个赞 4 个评论

然后对于 idea id 3，结果返回为 0。

虽然我希望它这样排序

idea id 1(since 3+3)
idea id 3(since 0+4)
idea id 2(since 1+2)

Answer 1

SELECT  a.idea_id,
        a.idea_name,
        COUNT(DISTINCT b.like_ID) totalLikes,
        COUNT(DISTINCT c.comm_ID) totalComment
FROM    td_idea a
        LEFT JOIN td_idea_like b
            ON a.idea_ID = b.idea_ID
        LEFT JOIN td_idea_comment c
            ON a.idea_ID = c.idea_ID
GROUP   BY a.idea_id, a.idea_name
ORDER   BY COUNT(DISTINCT b.like_ID) + COUNT(DISTINCT c.comm_ID) DESC

• SQLFiddle Demo

要进一步了解有关联接的更多信息，请访问以下链接:

• Visual Representation of SQL Joins