mysql - SQLite 在 WHERE 中传递多个参数

Question

我有一个在 MYSQL 中运行但不会在 SQLite 中运行的查询。我认为它失败是因为 WHERE 子句中的多个参数。

目的是为所有调查问题 (sqid) 选择唯一的最新调查答案 (said)，因为一个问题可能有多个答案。

SELECT sqid, said, saname, satimestamp
FROM surveyanswer 
WHERE (sqid, hoid, satimestamp) IN (
    SELECT sqid, hoid, max(satimestamp) as satimestamp
    FROM surveyanswer
    GROUP BY sqid
)

我尝试过按 satimestamp 排序，并选择了一个限制而不是最大值，但无法让它工作。

Answer 1

您可以通过连接来做到这一点:

SELECT sqid, said, saname, satimestamp
FROM surveyanswer A
JOIN (
    SELECT sqid, hoid, max(satimestamp) as satimestamp
    FROM surveyanswer
    GROUP BY sqid
) B ON A.sqid = B.sqid and A.hoid = B.hoid and A.satimestamp = B.satimestamp

这在功能上与您的原始 SQL 类似，但由于您只关心时间，我相信这也能正常工作:

SELECT sqid, said, saname, satimestamp
FROM surveyanswer A
JOIN (
    SELECT max(satimestamp) as satimestamp
    FROM surveyanswer
) B ON A.satimestamp = B.satimestamp