mysql - SQL : select top 4 results with the higher votes UP and lower votes DOWN

Question

我喜欢做一些简单的事情，但我不知道从哪里开始。

所以我有 4 张 table :

• Table question (id_question)
• Table trad (id_trad, #id_question)
• Table vote_up (id_vote_up, #id_trad)
• Table vote_down (id_vote_down, #id_trad)

对于 id_question=1，我想选择 vote_up 数量较高且 vote_down 数量较低的 4 个翻译(来自 trad)

是否可以通过单个查询来完成此操作？有什么想法吗？

或者也许简单地添加“upvotes”和“downvotes”并将相应字段更新 1 更好？

Answer 1

计划

• get count of upvotes grouped by each trad
• get count of downvotes grouped by each trad
• left join all trad to above datasources with score function, order by and limit of 4

<小时/>

输入示例

create table question
(
  id_question integer primary key not null
  -- other metadata here..
);

create table trad
(
  id_trad integer primary key not null,
  id_question integer not null,
  foreign key ( id_question ) references question ( id_question )
);

create table vote_up
(
  id_vote_up integer primary key not null,
  id_trad integer not null,
  foreign key ( id_trad ) references trad ( id_trad )
);

create table vote_down
(
  id_vote_down integer primary key not null,
  id_trad integer not null,
  foreign key ( id_trad ) references trad ( id_trad )
);

insert into question
( id_question )
values
( 1 )
;

insert into trad
( id_trad, id_question )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 1 )
;

insert into vote_up
( id_vote_up, id_trad )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 3 ),
( 8, 3 ),
( 9, 3 ),
( 10, 3 ),
( 11, 4 ),
( 12, 4 ),
( 13, 5 ),
( 14, 6 ),
( 15, 6 ),
( 16, 7 ),
( 17, 7 ),
( 18, 7 )
;

insert into vote_down
( id_vote_down, id_trad )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 3 ),
( 8, 3 ),
( 9, 3 ),
( 10, 4 ),
( 11, 4 )
;

<小时/>

查询

select trad.id_trad, coalesce(upvotes, 0) - coalesce(downvotes, 0) as score
from trad
left join
(
select 
trad.id_trad, count(*) as upvotes
from trad
inner join vote_up
on trad.id_trad = vote_up.id_trad
group by 1
) uv
on trad.id_trad = uv.id_trad
left join
(
select 
trad.id_trad, count(*) as downvotes
from trad
inner join vote_down
on trad.id_trad = vote_down.id_trad
group by 1
) dv
on uv.id_trad = dv.id_trad
where trad.id_question = 1
order by score desc
limit 4
;

输出

+---------+-------+
| id_trad | score |
+---------+-------+
|       7 |     3 |
|       6 |     2 |
|       3 |     1 |
|       5 |     1 |
+---------+-------+

<强> sqlfiddle ( separate structures )

<小时/>

注意

consider also restructuring your votes into one table. atm vote_up and vote_down are unnecessarily duplicating the same structure.. this would look like :

<强> sqlfiddle ( reuse structure )