SQL SERVER 中UNION UNION ALL用法,并且子句中Order by用法

首先给大家推荐一下我老师大神的人工智能教学网站。教学不仅零基础,通俗易懂,而且非常风趣幽默,还时不时有内涵黄段子!点这里可以跳转到网站

CREATE table T_Test(  Id int not null ,  name nvarchar(50) not null) INSERT INTO T_Test VALUES (1,'a')INSERT INTO T_Test VALUES (2,'-')INSERT INTO T_Test VALUES (3,'b')INSERT INTO T_Test VALUES (4,'c')INSERT INTO T_Test VALUES (4,'+') /*SELECT  *  from  T_TestId name1 a2 -3 b4 c4 + */

一、问题:如果想要得到一个结果集是T_Test按照name正序排列,并且特殊符号放置字母后 


1.

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) aunion ALLSELECT * FROM (SELECT TOP 100 percent *  from T_Test where name < 'A' ORDER BY name ) b

结果为:

Id name
1 a
3 b
4 c
2 –
4 +
 

说明:union ALL不去重不排序,所以效率比union高,符合结果




2.

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) aunion SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name < 'A' ORDER BY name ) b

结果为:

Id name
1 a
2 –
3 b
4 +
4 c

说明:UNION去重且排序,Union将会按照字段的顺序进行排序,这样结果不是想要的,

如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话,那么就使用UNION ALL。
 

二、关于union,或union all子句中排序ORDER BY用法,下面写法是语法错误的

SELECT  *  from T_Test where name >= 'A' ORDER BY name union ALLSELECT  *  from T_Test where name < 'A' ORDER BY name


以下面sql为例

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) aunion ALLSELECT * FROM (SELECT TOP 100 percent *  from T_Test where name < 'A' ORDER BY name ) b

正确结果,必须要嵌套一层SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= ‘A’ ORDER BY name ) a

如果去掉“TOP 100 percent”,则会抛出异常“除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效”

点这里可以跳转到人工智能网站

发表评论