基于no.of行的复合查询解决方案

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  |
|-----------|------------|----------|

现在，我需要使用一个查询，它将以基于no的升序顺序对想法进行排序。td_idea_like和no的投票结果。关于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 3有0，喜欢，4注释

然后，对于ID3，结果返回为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演示

若要获得更多关于联接的知识，请访问以下链接：

• SQL连接的可视化表示