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

Dear 丶 2022-05-29 02:44 459阅读 0赞

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_Test
    
    Id name
    1 a
    2 -
    3 b
    4 c
    4 +
     */

![SouthEast][]

### **一、问题：如果想要得到一个结果集是T\_Test按照name正序排列，并且特殊符号放置字母后 ** ###

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) a
    union ALL
    SELECT * 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高，符合结果

![SouthEast 1][]  
  
  
2.

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) a
    union 
    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

![SouthEast 2][]

说明：UNION去重且排序，Union将会按照字段的顺序进行排序，这样结果不是想要的，  
  
如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

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

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

![SouthEast 3][]  
以下面sql为例

SELECT * FROM (SELECT TOP 100 percent *  from T_Test where name >= 'A' ORDER BY name ) a
    union ALL
    SELECT * 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 子句在视图、内联函数、派生表、子查询和公用表表达式中无效”

![SouthEast 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[SouthEast]: /images/20220529/bbba0e2460314c64a4920587cef5cf7e.png
[SouthEast 1]: /images/20220529/da58d3fbccc747738c84bf357790ef86.png
[SouthEast 2]: /images/20220529/bbea3eea87cb4fbf9bc0d4d6babf33a5.png
[SouthEast 3]: /images/20220529/2966bbd459b64b9d831518311832b956.png
[SouthEast 4]: /images/20220529/bf503950dbd24c439fc39778159d209f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220529/5acbee4ad7ab41588a6bd9ea0ddd185c.png