SQL查询性能和连接表

I am struggling to get the best query for my Q&A website. As a simplified example, I have 3 tables (questions, answers and users):

我正在努力为我的问答网站找到最好的查询。作为一个简化的示例，我有3个表(问题、答案和用户):

table_users

table_users

UserID  FirstName
1       John
2       Jack
3       Ana
4       Mary

table_questions

table_questions

QuestionID  fkUserID Title
1           4        What is...?
2           2        Where is...?
3           1        How to...?
4           3        How much...?

table_answers

table_answers

AnswerID    fkQuestionID    fkUserID  Answer
1           1               3         It is...
2           2               1         It is located in ZZ...
3           2               4         It is located in YY...
4           3               2         You have to...

I am creating a newsfeed to:

我正在创建一个新闻提要:

Show the last answers ordered by the added date
显示按添加日期排序的最后答案
If the there are 2 answers for the same question, the newsfeed will only show the last answer (avoid duplication). In the example the newsfeed would only show the AnswerID 3 (It is located in YY...) and would skip the AnswerID 2.
如果同一个问题有两个答案，新闻提要将只显示最后的答案(避免重复)。在示例中，newsfeed只显示AnswerID 3(它位于YY…)，并跳过AnswerID 2。
The newsfeed will show the first name of the person that created the question as well the person that answered it, both are located in the same table of users
newsfeed将显示创建问题的人和回答问题的人的名字，两者都位于同一个用户表中

So far, I created this query below, however:

到目前为止，我创建了以下查询:

I am having some performance/speed issues and I am not being able to only show the last answer of a question (issue 2), the DISTINCT select doesn't work as expected in this case.

我有一些性能/速度问题，我不能只显示问题的最后答案(问题2)，不同的选择在这种情况下不能正常工作。

SELECT 
DISTINCT (A.fkQuestionID) as QuestionID,
Q.Title,
A.AnswerID as AnswerID,
A.Answer,
U1.FirstName as AnswerFirstName,
Q.pkQuestionID,
U2.FirstName as QuestionFirstName

FROM table_answers as A
INNER JOIN table_questions as Q ON Q.QuestionID=A.fkQuestionID
INNER JOIN table_users as U1 ON U1.UserID=A.fkUserID
INNER JOIN table_users as U2 ON U2.UserID=Q.fkUserID
ORDER BY A.AnswerID DESC

I'd like to return the expected result as:

我想把预期的结果作为:

QuestionID  AnswerID  AnswerFirstName   QuestionFirstName
3           4         Jack              John
2           3         Mary              Jack
1           1         Ana               Mary

Any help would be very welcome. Thanks in advance!

任何帮助都将受到欢迎。提前谢谢!

3 个解决方案

#1

You can use MAX inside a subquery and do JOINs to get the needed columns:

您可以在子查询中使用MAX，并进行连接以获取所需的列:

DEMO

演示

SELECT
    q.QuestionID,
    q.title,
    u1.FirstName AS Asker,
    a.AnswerID,
    a.answer,
    u2.FirstName AS Answerer
FROM table_questions q
INNER JOIN table_users u1
    ON u1.UserID = q.fkUserID
INNER JOIN (
    SELECT 
        fkQuestionID, MAX(AnswerID) AS AnswerID
    FROM table_answers
    GROUP BY fkQuestionID
) t
    ON t.fkQuestionID = q.QuestionID
INNER JOIN table_answers a
    ON a.AnswerID = t.AnswerID
    AND a.fkQuestionID = t.fkQuestionID
INNER JOIN table_users u2
    ON u2.UserID = a.fkUserID

Replace the INNER JOIN with LEFT JOIN if you wish to include questions without answers.

如果您希望包含没有答案的问题，请将内部连接替换为左连接。

#2

You can group by fkQuestionID to get the max(AnswerID) and then use it in further join like this.

您可以通过fkQuestionID分组以获取最大值(AnswerID)，然后将其用于类似这样的进一步连接。

SELECT 
q.QuestionID,
q.Title,
a1.AnswerID as AnswerID,
a1.Answer,
U1.FirstName as AnswerFirstName,
q.pkQuestionID,
U2.FirstName as QuestionFirstName
FROM
(SELECT MAX(AnswerID) as AnswerID,fkQuestionID FROM table_answers GROUP BY fkQuestionID ) as a2
INNER JOIN table_answers a1  ON a1.fkQuestionID = a2.fkQuestionID AND a1.AnswerID = a2.AnswerID
INNER JOIN table_questions q ON q.QuestionID = a2.fkQuestionID 
INNER JOIN table_users as U1 ON U1.UserID=a1.fkUserID
INNER JOIN table_users as U2 ON U2.UserID=q.fkUserID
ORDER BY a2.AnswerID DESC

#3

Plz try like this

请尝试这样

SELECT  QuestionID ,fkUserID,title,ANS.Answer
FROM    table_questions QST
INNER JOIN (SELECT fkQuestionID,MAX(AnswerID) AnswerID
            FROM    table_answers
            GROUP BY fkQuestionID) ANS ON ANS.fkQuestionID = QST.QuestionID
INNER JOIN table_answers LANS ON LANS.AnswerID = ANS.AnswerID

#1