我正在做SQL练习,收到了一个关于WHERE
子句的问题。
- Write an SQL query to find all numbers that appear at least three times consecutively.
*Logs table:*
id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2
我的sql查询代码是:
SELECT DISTINCT a.Num AS ConsecutiveNums FROM Logs AS a
INNER JOIN Logs AS b ON a.Id + 1 = b.Id
INNER JOIN Logs AS c ON a.Id + 2 = c.Id
WHERE a.Num = b.Num = c.Num;
当我把条件写为WHERE a.Num = b.Num = c.Num
时,结果显示{"headers": ["ConsecutiveNums"], "values": [[1], [2]]}
,这是不正确的。但当我将条件分离为WHERE a.Num = b.Num AND b.Num = c.Num
时,结果是{"headers": ["ConsecutiveNums"], "values": [[1]]}
,这是正确的。
关于这种情况,有人能告诉我们WHERE子句的原理吗?
正确的方法是使用:
WHERE a.Num = b.Num AND b.Num = c.Num
使用时:
WHERE a.Num = b.Num = c.Num
你得到的结果是巧合
它与运算符=
和MySql的特性有关,以x = y
等布尔表达式作为true
的1
和false
的0
因此,这个布尔表达式:
a.Num = b.Num = c.Num
评估为:
(a.Num = b.Num) = c.Num
由于(a.Num = b.Num)
的结果可以是0
或1
,因此整个表达式等价于:
0 = c.Num
或:
1 = c.Num
但是表中没有Num = 0
的行,所以布尔表达式计算为1
(true
(的唯一情况是如果Num = 1
,这是您得到的结果(巧合(
SELECT DISTINCT NUM
FROM Logs as main
WHERE
NUM = (SELECT NUM FROM Logs WHERE ID = main.ID +1)
AND NUM = (SELECT NUM FROM Logs WHERE ID = main.ID +2)
由于这看起来像是一个家庭作业,我希望您了解查询的每个部分是如何工作的,以及它为什么工作。
select distinct l1.Num as ConsecutiveNums
from
Logs l1,
Logs l2,
Logs l3
where l1.Id = l2.Id-1
and l2.Id = l3.Id-1
and l1.Num=l2.Num
and l2.Num=l3.Num
希望这能帮助您