计算每个帖子的投票数

发布于 2024-12-13 11:13:17 字数 717 浏览 0 评论 0原文

我们目前正试图进行一个简单的（？）SQL 查询。

我们有这两个表：

create table `post` (
    `id` integer primary key
)
create table `vote` (
    `id` integer primary key,
    `post_id` references `post`.`id`, // Well ok, it's a foreign key then...
    `value` int // 1 for a positive vote, or zero for a negative one
)

我们正在尝试构建一个选择，对于每个帖子，返回正面和负面投票的数量：SELECT post.id,,; ...

虽然这对于子选择来说并不难做到，但当我们尝试在不使用子选择但使用 join 的情况下做到这一点时，挑战就开始了。我们使用左外连接，当帖子只有赞成票或反对票时就会出现问题。

虽然我理解这个问题，但我无法弄清楚如何仅使用 join 来做到这一点，但我相信它可以在没有子选择的情况下完成。你会怎么做？

（好吧，我没有包含我当前的查询，这样它就不会引导您走向错误的方向......）

原文

We're currently stuck trying to make a simple (?) SQL query.

We have these two tables:

create table `post` (
    `id` integer primary key
)
create table `vote` (
    `id` integer primary key,
    `post_id` references `post`.`id`, // Well ok, it's a foreign key then...
    `value` int // 1 for a positive vote, or zero for a negative one
)

We're trying to build a select that would, for each post, return the number of positive and negative votes: SELECT post.id, <positive count>, <negative count> ...

While this is not hard to do with sub-selects, the challenge begins when we try to do this without sub-selects, but with join. We're using left outer join, and the problems arise when a post has only positive or negative votes.

While I understand the problem, I can't figure out how to do that only with join, yet I'm confident it can be done without sub-selects. How would you do that ?

(Well, I did not include my current query, so that it won't guide you to the wrong direction...)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

隔纱相望 2024-12-20 11:13:17

以前的答案是垃圾，我忘了GROUP。这是使用 CASE 的替代方案，我认为它可以处理 NULL 情况（因为 WHEN 子句将为 NULL ）但毕竟你可能需要使用COALESCE...：

SELECT post.id, SUM(CASE WHEN a.vote > 0 THEN 1 ELSE 0 END) up, SUM(CASE WHEN a.vote < 0 THEN 1 ELSE 0 END) down FROM post LEFT JOIN vote a ON (a.post_id = post.id) GROUP BY post.id

以前（不正确）的答案：

听起来您需要使用COALESCE来强制NULL为零 - 以下可能有效，但未经测试：
SELECT post.id, SUM(COALESCE(a.vote,0)), SUM(COALESCE(b.vote,0)) FROM post LEFT JOIN vote a ON (a.post_id = post.id AND值 > 0) 左连接投票 b ON (b.post_id = post.id AND 值 < 0)

Previous answer was rubbish, I forgot to GROUP. Here's an alternative that uses CASE, I THINK it handles the NULL case (since the WHEN clause will be NULL) but you may need to use COALESCE after all...:

SELECT post.id, SUM(CASE WHEN a.vote > 0 THEN 1 ELSE 0 END) up, SUM(CASE WHEN a.vote < 0 THEN 1 ELSE 0 END) down FROM post LEFT JOIN vote a ON (a.post_id = post.id) GROUP BY post.id

Previous (incorrect) answer:

It sounds like you need to use COALESCE to force a NULL to zero - the following might work but is untested:
SELECT post.id, SUM(COALESCE(a.vote,0)), SUM(COALESCE(b.vote,0)) FROM post LEFT JOIN vote a ON (a.post_id = post.id AND value > 0) LEFT JOIN vote b ON (b.post_id = post.id AND value < 0)

回复收藏 0 原文

~没有更多了~