sql - SQL where/or 混淆

Question

我第一次在一个巨大的数据库上运行 sql 语句，我有这样的代码。

Select x, sum(y), sum(z) from db
 where n = 'xxx' or n = 'yyy' and m = int
group by x

现在如果我这样做

Select x, sum(y), sum(z) from db
     where n = 'xxx' and m = int
    group by x
Select x, sum(y), sum(z) from db
     where n = 'yyy' and m = int
    group by x

并从两个表中手动将分组值添加在一起，我在查询中得到不同的结果，分离的查询更准确。

EG 第一个查询中第 1 行的结果将是 2000 万，在第二个代码块中将第 1 行加在一起的结果将是 1800 万？不知道是什么问题...？

标签： sqlsql-server

Answer 1

使用in. 您的代码实际上没有意义：

where n in ('xxx', 'yyy') and m = int

这个查询：

where n = 'xxx' or 'yyy' and m = int

应该在 SQL Server 中返回错误，因为'yyy'. MySQL 接受这种语法。在该数据库中，它将被处理为：

where n = 'xxx' or 'yyy' and m = int

   -- AND has higher precedence than `or`
where n = 'xxx' or ('yyy' and m = int)

   -- `'yyy'` is converted to an integer
where n = 'xxx' or (0 and m = int)

   -- which is treated as a boolean
where n = 'xxx' or (false and m = int)

   -- which is grouped like this
where n = 'xxx' or (false and (m = int))

   -- which is equivalent to
where n = 'xxx'