SQL - 如何在UNION查询中排序

Is there a way to union two tables, but keep the rows from the first table appearing first in the result set? However orderby column is not in select query

有没有办法结合两个表，但保持第一个表中的行首先出现在结果集中？但是，orderby列不在select查询中

For example:

例如：

Table 1

表格1

name        surname
-------------------
John         Doe
Bob          Marley
Ras          Tafari

Table 2

表2

name       surname
------------------
Lucky      Dube
Abby       Arnold
Result

Expected Result:

预期结果：

name        surname
-------------------
John         Doe
Bob          Marley 
Ras          Tafari
Lucky      Dube
Abby       Arnold

I am bringing Data by following query

我通过以下查询来提供数据

SELECT name,surname FROM TABLE 1 ORDER BY ID  
UNION  
SELECT name,surname FROM TABLE 2

The above query is not keeping track of order by after union.
P.S - I dont want to show ID in my select query
I am getting ORDER BY Column by joining tables. Following is my real query

上述查询不会在联合之后跟踪订单。 P.S - 我不想在我的选择查询中显示ID我通过连接表获得ORDER BY Column。以下是我的真实查询

  SELECT tbl_Event_Type_Sort_Orders.Appraisal_Event_Type_ID AS Appraisal_Event_Type_ID , ISNULL(tbl_Appraisal_Event_Types.Appraisal_Event_Type_Display_Name, 'UnCategorized') AS  Appraisal_Event_Type_Display_Name 
INTO #temptbl
FROM tbl_Event_Type_Sort_Orders 
INNER JOIN tbl_Appraisal_Event_Types
ON tbl_Event_Type_Sort_Orders.Appraisal_Event_Type_ID = tbl_Appraisal_Event_Types.Appraisal_Event_Type_ID
WHERE 1=1
AND User_Name='abc' 
ORDER BY tbl_Event_Type_Sort_Orders.Sort_Order

SELECT * FROM #temptbl 
UNION 
SELECT DISTINCT (tbl_Appraisal_Event_Types.Appraisal_Event_Type_ID) AS Appraisal_Event_Type_ID , ISNULL(tbl_Appraisal_Event_Types.Appraisal_Event_Type_Display_Name, 'UnCategorized') AS  Appraisal_Event_Type_Display_Name 
FROM tbl_Appraisal_Event_Types
INNER JOIN tbl_Appraisal_Events
ON tbl_Appraisal_Event_Types.Appraisal_Event_Type_ID = tbl_Appraisal_Events.Event_Type_ID
INNER JOIN tbl_Appraisals 
ON tbl_Appraisal_Events.Appraisal_ID = tbl_Appraisal_Events.Appraisal_ID
WHERE 1=1
AND ((tbl_Appraisals.Assigned_To_Staff_User) = 'abc'  OR (tbl_Appraisals.Assigned_To_Staff_User2) = 'abc' OR (tbl_Appraisals.Assigned_To_Staff_User3) = 'abc')

5 个解决方案

#1

Put a UNION ALL in a derived table. To keep duplicate elimination, do select distinct and also add a NOT EXISTS to second select to avoid returning same person twice if found in both tables:

将UNION ALL放在派生表中。要保持重复消除，请选择distinct并在第二个选择中添加NOT EXISTS以避免在两个表中找到两次返回同一个人：

select name, surname
from
(
    select distinct name, surname, 1 as tno
    from table1
    union all
    select distinct name, surname, 2 as tno
    from table2 t2
    where not exists (select * from table1 t1
                      where t2.name = t1.name
                        and t2.surname = t1.surname)
) dt
order by tno, surname, name

#2

You can write as below, if you are ok with duplicate data then please use UNION ALL it will be faster:

您可以写如下，如果您对重复数据没问题那么请使用UNION ALL它会更快：

SELECT NAME, surname FROM (
SELECT ID,name,surname FROM TABLE 1 
UNION
SELECT ID,name,surname FROM TABLE 2 ) t ORDER BY ID

#3

You can use a column for the table and one for the ID to order by:

您可以使用表格的列和要按以下顺序排列的ID：

SELECT x.name, x.surname FROM (
    SELECT ID, TableID = 1, name, surname
    FROM table1

    UNION  ALL

    SELECT ID = -1, TableID = 2, name, surname
    FROM table2
) x
ORDER BY x.TableID, x.ID

#4

this will order the first row sets first then by anything you need (haven't tested the code)

这将首先按照您需要的任何内容排序第一行集（尚未测试代码）

;with cte_1
as 
 (SELECT ID,name,surname,1 as table_id FROM TABLE 1 
  UNION
  SELECT ID,name,surname,2 as table_id FROM TABLE 2 ) 
SELECT name, surname 
FROM cte_1
ORDER BY table_id,ID

#5

simply use a UNION clause with out order by.

只需使用UNION子句，而不是按顺序。

   SELECT name,surname FROM TABLE 1 
   UNION
   SELECT name,surname FROM TABLE 2

if you wanted to order first table use the below query.

如果您想订购第一个表，请使用以下查询。

;WITH cte_1
AS
 (SELECT name,surname,ROW_NUMBER()OVER(ORDER BY  Id)b FROM TABLE 1 )
SELECT name,surname
FROM cte_1
UNION 
SELECT name,surname 
FROM TABLE 2

#1