mysql - SQL - MAX 函数返回不正确的数据

Question

我试图解决每个具有最大 seq_no 记录的问题，但它返回了错误的数据。

这里是查询:

SELECT id, attempt, seq_number, question
FROM question_states
WHERE attempt = 374
AND seq_number IN ( SELECT MAX(seq_number) FROM question_states WHERE attempt = 374 GROUP BY question)
GROUP BY question, id

我有一个表名作为问题，结构如下:-

id--seq_number--attempt--question
1 --0     --374  -- 130
2 --0     --374  -- 130
3 --1     --374  -- 130
4 --2     --374  -- 130
5 --1     --374  -- 131

为什么此查询针对问题 130 返回 2 条记录？如果我删除 GROUP BY id 然后返回错误的数据。

Answer 1

试试这个:

SELECT id, attempt, seq_number, question
FROM question_states qs 
INNER JOIN (SELECT question, MAX(seq_number) seq_number 
            FROM question_states WHERE attempt = 374 GROUP BY question
           ) AS A ON qs.question = A.question AND qs.seq_number = A.seq_number 
WHERE attempt = 374

或

SELECT id, attempt, seq_number, question
FROM (SELECT id, attempt, seq_number, question
      FROM question_states WHERE attempt = 374
      ORDER BY question, seq_number DESC
     ) AS A 
GROUP BY question