在sqlite版本3.7.12.1(控制台)中,此查询不起作用:
(SELECT * FROM A UNION SELECT * FROM B) EXCEPT (SELECT * FROM A INTERSECT SELECT * FROM B);
错误信息
Error: near line 1: near “(“: Syntax error
此查询适用于sql Server Management Studio.带括号的其他查询可以按预期工作.我错过了什么吗?
编辑:澄清:
SELECT * FROM A; <-- works (SELECT * FROM A); <-- does not work [Error: near line 1: near "(": Syntax error] SELECT * FROM A WHERE A.id IN (SELECT B.id FROM B); <-- works,so no fundamental issues with brackets and sqlite...
似乎sqlite不喜欢将组合(子)查询(具有UNION,UNION ALL,EXCEPT或INTERSECT的查询)放在括号中:
原文链接:https://www.f2er.com/sqlite/197705.html>这不起作用:
(SELECT 1 AS v UNION SELECT 2) EXCEPT SELECT 1
>这也不起作用:
SELECT 1 AS v UNION (SELECT 2 EXCEPT SELECT 1)
并且没有括号,各个子选择按顺序组合,即对于任何其他sql产品中的任何运算符都没有固有的优先级. (例如,这个
SELECT 1 AS v UNION SELECT 2 INTERSECT SELECT 3
sql Server中的returns 1
(因为首先执行INTERSECT)和sqlite中的nothing.)
唯一的解决方法似乎是使用要组合的部分,作为子查询,like this:
SELECT * FROM ( SELECT * FROM A UNION SELECT * FROM B ) EXCEPT SELECT * FROM ( SELECT * FROM A INTERSECT SELECT * FROM B )