SQLite 支架“不起作用" [英] SQLite Bracket "don't work"

问题描述

在 SQLite 版本 3.7.12.1(控制台)中，此查询不起作用:

In SQLite version 3.7.12.1 (console) this query does not work:

(SELECT * FROM A
UNION
SELECT * FROM B)
EXCEPT
(SELECT * FROM A
INTERSECT
SELECT * FROM B);

错误信息

错误:第 1 行附近:("附近:语法错误

Error: near line 1: near "(": syntax error

此查询适用于 SQL Server Management Studio.其他带括号的查询确实按预期工作.我错过了什么吗?

This query works in SQL Server Management Studio. Other queries with brackets do work as expected. Am I missing something?

澄清:

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) 括起来:

Seems like SQLite doesn't like combined (sub)queries (those with UNION, UNION ALL, EXCEPT or INTERSECT) to be bracketed:

• 这不起作用:

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

这也不起作用:

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

(但是 两者 在 SQL Server 中工作.)

并且没有括号，各个子选择按顺序组合，即与其他一些 SQL 产品一样，任何运算符都没有固有的优先级.(例如，这个

And without brackets, the individual subselects are combined sequentially, i.e. there's no inherent priority to any of the operators like in some other SQL products. (For instance, this

SELECT 1 AS v
UNION
SELECT 2
INTERSECT
SELECT 3

在 SQL Server 中返回 1(因为 INTERSECT 首先执行)和 nothing 在 SQLite 中.)

returns 1 in SQL Server (because INTERSECT is performed first) and nothing in SQLite.)

唯一的解决方法似乎是使用您想要组合的部分，作为子查询，like这个:

The only workaround seems to be to use the parts you want to combine, as subqueries, like this:

SELECT *
FROM (
  SELECT * FROM A
  UNION
  SELECT * FROM B
)
EXCEPT
SELECT *
FROM (
  SELECT * FROM A
  INTERSECT
  SELECT * FROM B
)

这篇关于SQLite 支架“不起作用"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！