mysql - 如何选择获得超过特定票数的帖子？

Question

我的表格结构:

// posts
+----+----------------------------------------+-----------+
| Id |                   body                 |  user_id  |
+----+----------------------------------------+-----------+
| 1  | content1                               | 2         |
| 2  | content2                               | 9         | 
| 3  | content3                               | 6         |
| 4  | content4                               | 4         | 
| 5  | content5                               | 2         |
| 6  | content6                               | 8         |
| 7  | content7                               | 4         | 
| 8  | content8                               | 2         | 
+----+----------------------------------------+-----------+

// votes
+----+---------+-------+
| id | post_id | value |
+----+---------+-------+
| 1  | 2       |  1    |
| 2  | 3       | -1    |
| 3  | 2       |  1    |
| 4  | 8       | -1    |
| 5  | 1       |  1    |
| 6  | 8       |  1    |
| 7  | 2       | -1    |
| 8  | 8       | -1    |
| 9  | 2       |  1    |
+----+---------+-------+

我需要选择总得分超过 1 的帖子。所以这是预期的输出:

+----+----------------------------------------+-----------+-------------+
| Id |                   body                 |  user_id  | total_votes |
+----+----------------------------------------+-----------+-------------+
| 2  | content2                               | 9         | 2           |
+----+----------------------------------------+-----------+-------------+

我该怎么做？

Answer 1

create table qanda
(   id int not null,
    body varchar(100) not null,
    user_id int not null
);
insert qanda values
(1,'a',2),
(2,'a',9),
(3,'a',6),
(4,'a',4),
(5,'a',2),
(6,'a',8),
(7,'a',2),
(8,'a',2);

create table votes
(   id int not null,
    post_id int not null,
    value int not null
);
insert votes values
(1,2,1),
(2,3,-1),
(3,2,1),
(4,8,-1),
(5,1,1),
(6,8,1),
(7,2,-1),
(8,8,-1),
(9,2,1);

查询

 select q.id,q.body,q.user_id,sum(v.value) as votes 
 from qanda q 
 join votes v 
 on v.post_id=q.id 
 group by q.id,q.body,q.user_id
 having votes>1;


+----+------+---------+-------+
| id | body | user_id | votes |
+----+------+---------+-------+
|  2 | a    |       9 |     2 |
+----+------+---------+-------+