我有这样的表格设置:
用户表:
ID | Username | Password
1 | Humza | 123
3 | God | 145
问题表:
Question_ID| Question_Title | Question | Rating | Category ID | User ID |
1 | Testing Display | Did it Work | 55 | 2 | 3 |
2 | Foo is love? | Same as Above | -100 | 2 | 1 |
3 | Bla | Baa Black XD | 909 | 2 | 1 |
答题表:
Answer_ID| Answer | Answer_Rating | Answer_User_ID | Question_ID |
1 | YES | 909 | 1 | 2 |
2 | Wohoo | 15 | 3 | 2 |
声誉表:
Reputation_ID| Reputation | User_Rep_ID|
1 | -50 | 1 |
2 | 9999 | 3 |
类别表:
Category_ID | Category |
1 | Dr Who |
2 | Programming |
3 | Music |
我的现有查询从类别 2 中挑选问题:
SELECT u.username, r.Reputation, q.Question_Title,
q.Question, q.Rating, c.Category, count(a.Answer_ID) as num_answers
FROM Questions q
INNER JOIN Users u ON u.id = q.User_ID
INNER JOIN Reputation r on r.User_Rep_ID = u.id
INNER JOIN Categorys c ON c.Category_ID = q.Category_ID
LEFT OUTER JOIN Answers a on q.Question_ID = a.Question_ID
WHERE q.Category_ID = 2
GROUP BY a.Question_ID
然而,由于某种原因,这不会挑出第三个问题,所以它并不完美。
当前查询以挑选答案:
SELECT u.username as AnswerUserName,
a.Answer, a.Answer_Rating, r.Reputation as AnswerReputation
FROM Answers a
INNER JOIN Users u ON u.ID = a.Answer_User_ID
INNER JOIN Questions q on q.Question_ID = a.Question_ID
LEFT OUTER JOIN Reputation r ON r.User_Rep_ID = u.ID
WHERE a.Question_ID = q.Question_ID
然而,这似乎只是挑选出两个答案,无论问题是否有答案。 因此,当我查询类别 1 中的所有问题时,将显示相同的答案,我不确定为什么。
现在我将尽力解释:
1(我需要3个单独的查询,它将为我选择类别1,2和3中的所有问题以及发布它们的用户及其声誉。除此之外,它还必须计算每个类别中每个问题的答案数量。编辑 - 完成
2(我需要第二个查询,它将为我提供每个类别都有答案的问题的所有答案,回答问题的人的用户名和他们的声誉。
我将如何实现这一目标?如果您需要更多详细信息,请随时询问。
实际上,我不确定为什么这会起作用。 初始查询中有不在 GROUP BY 语句中的非聚合字段。 另外,由于您知道并非每个问题都有答案,因此我不会按A.Question_ID分组 - 而是按q.question_Id分组。 试试这个:
SELECT u.username, r.Reputation, q.Question_Title,
q.Question, q.Rating, c.Category, count(a.Answer_ID) as num_answers
FROM Questions q
INNER JOIN Users u ON u.id = q.User_ID
INNER JOIN Reputation r on r.User_Rep_ID = u.id
INNER JOIN Categorys c ON c.Category_ID = q.Category_ID
LEFT OUTER JOIN Answers a on q.Question_ID = a.Question_ID
WHERE q.Category_ID = 2
GROUP BY u.username, r.Reputation, q.Question_Title,
q.Question, q.Rating, c.Category