SQL多次连接计数，遇到麻烦 [英] SQL multiple join with count, having trouble

问题描述

首先，这是一项家庭作业，因此我在寻求帮助，而不是解决方案。让我尝试解释一下我的架构。我有三个表，分别称为用户（列ID和名称），聚会（列ID，partydate和user_id）和问题（列ID，createdate和user_id）。我的要求是向每个用户显示去年内的聚会数量以及去年内创建的问题。起初我有这样的事情：

First of all this is a homework assignment so I'm looking for assistance, not solutions. Let me try to explain my schema. I have three tables we'll call users (with columns id and name), parties (with columns id, partydate, and user_id) and questions (with columns id, createdate, and user_id). My requirement is to show for every user the number of parties within the last year and questions created within the last year. At first I had something like this:

SELECT users.id, users.name,  
COUNT(parties.id) AS numparties, COUNT(qustions.id) AS numquestions
FROM users
FULL JOIN parties ON users.id=parties.user_id
FULL JOIN questions ON users.id=questions.user_id
WHERE (parties.partydate > NOW() - interval '1 year' OR parties.partydate IS NULL)
OR (questions.createdate > NOW() - interval '1 year' OR questions.createdate IS NULL)
GROUP BY users.id, users.name

现在这几乎可以了！问题是，如果用户在过去一年中没有聚会或问题，那么他们根本不会出现在结果中。我希望这样的用户出现，我只是希望它为每个numparties和numquestions向他们显示0。

Now this works, almost! The problem is, if a user has no parties nor questions within the past year, they don't show up at all in the result. I want such a user to show up, I just want it to show them with 0 for each numparties and numquestions.

我想我在这里需要的是某种条件的计数，我只想在该方的聚会日期是过去一年之内的COUNT（parties.id），对于问题也是如此。我只是不确定该怎么做。我有一种解决问题的办法，基本上我在上面的查询中使用几乎相同的副本进行联合，但我对numparties和numquestions使用SUM（0），而我的WHERE语句正是日期< ; =代替>。我觉得这不是最好的解决方法。

What I think I need here is some sort of conditional counting, where I only want to COUNT(parties.id) WHERE that party's partydate is within the past year, and the same for questions. I'm just unsure how to do that. I have a hacky-workaround way to do what I want, where I basically UNION the above query with a near identical copy of itself, except I use SUM(0) for numparties and numquestions and my WHERE statement is just where the date is <= instead of >. I feel this is not the best way to go about it.

有没有正确的指针？谢谢您的帮助！

Any pointers in the right direction? Thanks for the help!

推荐答案

看看这个： http://www.w3schools.com/sql/sql_join_left.asp 。我认为这可能会为您指明正确的方向。

Take a look at this: http://www.w3schools.com/sql/sql_join_left.asp. I think it might point you in the right direction.

这篇关于SQL多次连接计数，遇到麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！