SQLite Bracket“不工作”

前端之家收集整理的这篇文章主要介绍了SQLite Bracket“不工作”前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
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的查询)放在括号中:

>这不起作用:

(SELECT 1 AS v
UNION
SELECT 2)
EXCEPT
SELECT 1

>这也不起作用:

SELECT 1 AS v
UNION
(SELECT 2
EXCEPT
SELECT 1)

(但是sql Server中的both work.)

并且没有括号,各个子选择按顺序组合,即对于任何其他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
)

猜你在找的Sqlite相关文章