我有两个主SELECT语句,它们在单独运行时返回所需的结果,但在组合时不返回所需结果。
查询1
这很好,返回了预期的结果。
SELECT feed_mode_id FROM user WHERE id=2;
+--------------+
| feed_mode_id |
+--------------+
| 1 |
+--------------+
查询2
这也很好。有时结果是空的,有时不是。
SELECT
answer.id AS answer_id
FROM
answer
WHERE
answer.question_id = (
SELECT
question.id
FROM
question
ORDER BY
datetime_added_utc DESC
LIMIT 1
)
AND answer.user_id = 2;
Empty set (0.00 sec)
查询1和查询2组合
当将它们组合成两个子SELECT语句时,如下所示,feed_mode_id
是NULL
,但x.feed_mode_id
的结果应该如查询1所示。这就是我对这些组合语句的工作原理缺乏理解。
SELECT
x.feed_mode_id,
IF (COUNT(y.answer_id) < 1, 0, 1) AS answered_question
FROM
(SELECT
user.feed_mode_id
FROM
user
WHERE
user.id = 2) AS x,
(SELECT
answer.id AS answer_id
FROM
answer
WHERE
answer.question_id = (
SELECT
question.id
FROM
question
ORDER BY
datetime_added_utc DESC
LIMIT 1
)
AND answer.user_id = 2) AS y
+--------------+-------------------+
| feed_mode_id | answered_question |
+--------------+-------------------+
| NULL | 0 |
+--------------+-------------------+
为什么feed_mode_id
产生NULL
而不是1
?我也对重写查询的不同方法持开放态度。期望的结果是:
+--------------+-------------------+
| feed_mode_id | answered_question |
+--------------+-------------------+
| 1 | 0 |
+--------------+-------------------+
这在某种程度上与查询2的结果在这种情况下为空这一事实有关。对于查询2返回值(不为空)的情况,则组合查询按需工作。
您在x
和y
之间有一个笛卡尔乘积。只要这些行源中的每一个只返回一行,查询就会返回一行。
我建议您在联接操作中放弃老式的逗号语法,改用JOIN
关键字。
此外,除了返回"最近添加的"问题的子查询之外,还不清楚为什么需要内联视图和子查询。
我不完全清楚您实际试图返回的结果集是什么,但看起来您正在确定某个特定用户(由"用户"表的"id"列唯一标识)是否为"最近添加的问题"提供了"答案"。
如果这是你试图返回的结果,我相信这个查询会返回结果:
SELECT u.feed_mode_id
, IF( COUNT(a.id) < 1, 0, 1) AS answered_question
FROM ( SELECT q.id
FROM question q
ORDER BY q.datetime_added_utc DESC, q.id DESC
LIMIT 1
) r
JOIN user u
ON u.id = 2
LEFT
JOIN answer a
ON a.user_id = u.id
AND a.question_id = r.id
GROUP BY u.id
注意:别名为r
的内联视图返回"最近添加的"question
的id
。(在原始查询中,如果两个或多个问题具有相同的datetime_added_utc
,则不确定将返回哪一行。此查询通过向ORDER by子句添加另一个表达式来确定。(内联视图查询可以被拉出并单独运行,以验证它是否返回了预期结果。)
然后,从r
返回的行(如果有)将连接到从"用户"表u
检索的行。这里我们假设"user"表中的id
列是一个唯一的标识符,很可能是"user"表格的主键。
如果我们有一个"最近添加的"问题(即来自r
的一行),并且来自"user"的一行与ON
子句中的u.id=2
谓词匹配,那么到目前为止,我们可以保证查询将返回一行。
接下来,我们执行一个"外部联接"操作,从"答案"表中查找匹配的行。ON
子句中的谓词将返回的行限制为只有user_id
与u
中的id
匹配的行(在本例中,相当于指定a.user_id=2
、和的question_id
与"最近添加的"问题r
的id
匹配。
LEFT
关键字将其标识为"外部联接";如果"answerswer"表中没有匹配的行,则查询仍将返回r
和u
中的行。(如果这是一个内部联接,也就是说,如果我们删除了LEFT
关键字,那么如果"answerswer"表中没有匹配的行,那么查询将不会返回一行。)
我们添加了一个GROUP BY u.id
子句,以防从answer
中获得多个匹配行;则CCD_ 34使得具有相同值u。
COUNT()
聚合统计"答案"表中id
的非空出现次数。如果没有找到匹配的行,那么a.id将为NULL,因此COUNT(a.id)将返回0。
如果我们正在寻找多路复用用户,如果我们为user.id指定了多个匹配值,例如,那么这个相同的查询也会起作用
ON u.id IN (2,3,5,7)
或者,如果我们完全省略了谓词,那么我们为每个用户返回一行。此查询仍然有效。
但在这两种情况下,我们都希望将u.id AS user_id
添加到查询的SELECT列表中,这样我们就可以知道哪一行是为哪个用户准备的。
如果我们想返回最近添加的两个问题,我们可以更改r
中的LIMIT子句,然后将r.id
添加到GROUP BY
子句中。同样,我们可能还想在SELECT列表中返回r.id AS question_id
,这样我们就知道哪一行是针对哪个问题的。
准确查询
SELECT
x.feed_mode_id,
IF (COUNT(y.answer_id) < 1, 0, 1) AS answered_question
FROM
(SELECT
user.feed_mode_id
FROM
user
WHERE
user.id = 2) AS x,
(SELECT
answer.id AS answer_id
FROM
answer
WHERE
answer.question_id IN (
SELECT
question.id
FROM
question
ORDER BY
datetime_added_utc DESC
LIMIT 1
)
AND answer.user_id = 2) AS y
answer.question_id=到中的answer.question_id
我不喜欢回答自己的问题,但我找到了一个有效的解决方案。我已经将IF语句移到了子SELECT,而不是主SELECT。我不知道为什么这有效,而之前的尝试没有,但现在它正在产生想要的结果。
SELECT
x.feed_mode_id,
y.question_answered
FROM
(SELECT
user.feed_mode_id
FROM
user
WHERE
user.id = 2) AS x,
(SELECT
IF (COUNT(answer.id < 1), 1, 0) AS question_answered
FROM
answer
WHERE
answer.question_id = (
SELECT
question.id
FROM
question
ORDER BY
datetime_added_utc DESC
LIMIT 1
)
AND answer.user_id = 2) AS y;
+--------------+-------------------+
| feed_mode_id | question_answered |
+--------------+-------------------+
| 1 | 0 |
+--------------+-------------------+