sql - 帮助进行 SQL 查询

Question

我的表:

suggestions:
suggestion_id|title|description|user_id|status|created_time

suggestion_comments:
scomment_id|text|user_id|suggestion_id

suggestion_votes:
user_id|suggestion_id|value

其中值是分配给投票的点数。

我希望能够选择:suggestion_id、标题、评论数和该建议的值的总和。按值的总和排序。限制 30

有什么想法吗？

Answer 1

您可能想尝试使用子查询，如下所示:

SELECT  s.suggestion_id,
        (   
           SELECT COUNT(*) 
           FROM suggestion_comments sc 
           WHERE sc.suggestion_id = s.suggestion_id
        ) num_of_comments,
        (   
           SELECT SUM(sv.value) 
           FROM suggestion_votes sv
           WHERE sv.suggestion_id = s.suggestion_id
        ) sum_of_values
FROM    suggestions s;

测试用例:

CREATE TABLE suggestions (suggestion_id int);
CREATE TABLE suggestion_comments (scomment_id int, suggestion_id int);
CREATE TABLE suggestion_votes (user_id int, suggestion_id int, value int);

INSERT INTO suggestions VALUES (1);
INSERT INTO suggestions VALUES (2);
INSERT INTO suggestions VALUES (3);

INSERT INTO suggestion_comments VALUES (1, 1);
INSERT INTO suggestion_comments VALUES (2, 1);
INSERT INTO suggestion_comments VALUES (3, 2);
INSERT INTO suggestion_comments VALUES (4, 2);
INSERT INTO suggestion_comments VALUES (5, 2);
INSERT INTO suggestion_comments VALUES (6, 3);

INSERT INTO suggestion_votes VALUES (1, 1, 3);
INSERT INTO suggestion_votes VALUES (2, 1, 5);
INSERT INTO suggestion_votes VALUES (3, 1, 1);
INSERT INTO suggestion_votes VALUES (1, 2, 4);
INSERT INTO suggestion_votes VALUES (2, 2, 2);
INSERT INTO suggestion_votes VALUES (1, 3, 5);

结果:

+---------------+-----------------+---------------+
| suggestion_id | num_of_comments | sum_of_values |
+---------------+-----------------+---------------+
|             1 |               2 |             9 |
|             2 |               3 |             6 |
|             3 |               1 |             5 |
+---------------+-----------------+---------------+
3 rows in set (0.00 sec)

---

更新: @Naktibalda's solution是避免子查询的替代解决方案。