一个基本的 sql 查询示例

Question

我有两个表：

NEWS (id, news_content)
NEWS_VOTES (vote, news_id)

我应该选择 (*) NEWS 上的所有值，并对 NEWS_VOTES 表上的投票进行计数，其中 news.id 和 news_votes.new_id 相同。

更清楚一点的解释是：

1. 我选择 NEWS 表上的所有值。
2. 根据“id”值，我还选择：

负面投票：

SELECT count(*) FROM NEWS_VOTES WHERE news_id = (same ID) AND vote = 0

正面投票：

SELECT count(*) FROM NEWS_VOTES WHERE news_id = (same ID) AND vote = 1

我需要在单个查询中执行此操作。

网站上的输出将类似于“此消息获得 57 票赞成，67 票反对”。

谢谢。

诗。我用的是MYSQL。

Answer 1

假设赞成票有 vote=1：

select 
  n.id,
  n.news_content,
  (select count(*) from news_votes where news_id=n.id and vote = 1) as positive_votes,
  (select count(*) from news_votes where news_id=n.id and vote = 0) as negative_votes
from news n

Answer 2

SELECT n.id, n.news_content,
       COUNT(v1.vote) AS negative,
       COUNT(v2.vote) AS positive
FROM news n
LEFT JOIN news_votes v1 ON v1.news_id = n.id
LEFT JOIN news_votes v2 ON v2.news_id = n.id
HAVING v1.vote = 0 AND HAVING v2.vote = 1
GROUP BY v1.news_id, v2.news_id
ORDER BY id DESC

Answer 3

select id, news_content, 
    coalesce(v.positive, 0) as positive, 
    coalesce(v.negative, 0) as negative
from news n
left join (
    select news_id, 
        sum(case when vote = 1 then 1 else 0 end) as positive,
        sum(case when vote = 0 then 1 else 0 end) as negative
    from news_votes
    group by news_id
) v on n.id = v.news_id

Answer 4

试试这个 -

SELECT
  n.*,
  COUNT(IF(nv.vote = 1, 1, NULL)) Positive_Votes,
  COUNT(IF(nv.vote = 0, 1, NULL)) Negative_Votes
FROM news n
  LEFT JOIN NEWS_VOTES nv
    ON nv.news_id = n.id
GROUP BY
  n.id

vote = 1 表示投赞成票， vote = 0 表示投反对票。

Answer 5

选择
(SELECT count(*) FROM NEWS_VOTES WHERE news_id = (same ID) AND vote = 0) Positive_votes,
(SELECT count(*) FROM NEWS_VOTES WHERE news_id = (same ID) AND vote = 1) negative_votes
来自双重；