为何Union All把相同的记录去除了？
Use tempdb;
Go
Create table [dbo].[a] (name varchar null);
Create table [dbo].[b] (name varchar null);
Create table [dbo].[c] (name varchar null);
Insert Into [dbo].[a] values ('a'),('b'),('c');
Insert Into [dbo].[b] values ('a'),('d'),('c');
Insert Into [dbo].[c] values ('a'),('e'),('f');
GO
Select name From dbo.a
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
Union All
Select name From dbo.b
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
Union All
Select name From dbo.c
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
明明应该有6条记录,以上代码返回5条记录，把相同name的c记录去掉了，为何呢？？ SQL
[解决办法]
不知道为什么，但是用这种可以
with t as
(
Select name From dbo.a except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
),t1 as
(
Select name From dbo.b except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
),t2 as
(
Select name From dbo.c except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
)
select * from t
union all
select * from t1
union all
select * from t2
[解决办法]
原因很简单，union 和 except、 intersect 的优先级是相同的，所以请使用括号：
Select name From dbo.a
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
)
Union All (
Select name From dbo.b
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
) )
Union All (
Select name From dbo.c
except (
Select name From dbo.a
Intersect
Select name From dbo.b
Intersect
Select name From dbo.c
))
[解决办法]
从执行计划的层次也就是执行的顺序来说，并不是先except/Intersect再union all的，之所以数据并不是你想要的是因为顺序本来就是你要的，所以运算时得不到你的结果，如果你要这样写，加上括号来保证优先级是对的。
StmtText
---------------------------------------------------------------------------------
[解决办法]
--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Union1037]))
[解决办法]
--Sort(DISTINCT ORDER BY:([Union1037] ASC))
[解决办法]
[解决办法]
--Concatenation
[解决办法]
[解决办法]
--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Union1020]))
[解决办法]
[解决办法]
[解决办法]
--Sort(DISTINCT ORDER BY:([Union1020] ASC))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Concatenation
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([tempdb].[dbo].[a].[name]))
[解决办法]
[解决办法]
------解决方案--------------------
[解决办法]
[解决办法]
--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[a].[name] ASC))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[a].[name]))
------解决方案--------------------
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[b]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[c]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[b]))
[解决办法]
[解决办法]
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
[解决办法]
[解决办法]
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([Union1020] = [tempdb].[dbo].[a].[name]))
[解决办法]
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[b]))
[解决办法]
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[c]))
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[c]))
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
[解决办法]
--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([Union1037] = [tempdb].[dbo].[a].[name]))
[解决办法]
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[b]))
[解决办法]
--Table Scan(OBJECT:([tempdb].[dbo].[c]))