Sql server联合但保持顺序

Is there a way to union two tables, but keep the rows from the first table appearing first in the result set?

有没有办法结合两个表，但保持第一个表中的行首先出现在结果集中？

For example:

例如：

Table1

表格1

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

Table2

表2

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

I want the result set to be:

我希望结果集为：

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

Unfortunately, union somehow reorders the table. Is there a way around this?

不幸的是，工会以某种方式重新排序表。有没有解决的办法？

4 个解决方案

#1

Try this :-

尝试这个：-

Select * 
from
( 
Select name,surname, 1 as filter
from  Table1
Union all
Select name,surname , 2 as filter
from Table2
)
order by filter

#2

The only way to guarantee output order is to use ORDER BY:

保证输出顺序的唯一方法是使用ORDER BY：

SELECT name,surname,1 as rs
FROM table1
UNION ALL
SELECT name,surname,2
FROM table2
ORDER BY rs

If you don't want rs to appear in the final result set, do the UNION as a subquery:

如果您不希望rs出现在最终结果集中，请将UNION作为子查询执行：

SELECT name,surname
FROM (
  SELECT name,surname,1 as rs
  FROM table1
  UNION ALL
  SELECT name,surname,2
  FROM table2
) t
ORDER BY rs

#3

;WITH cte as (
    SELECT name, surname, 1 as n FROM table1
    UNION ALL
    SELECT name, surname, 2 as n FROM table2
    UNION ALL
    SELECT name, surname, 3 as n FROM table3
)
SELECT name, surname
FROM cte
ORDER BY n;

#4

.Like this?

。喜欢这个？

CREATE TABLE #Table1 (Names VARCHAR(50))
CREATE TABLE #Table2 (Names VARCHAR(50))

INSERT INTO #Table1
(
    Names
)
VALUES
    ('John Doe'), ('Bob Marley'), ('Ras Tafari') 

INSERT INTO #Table2
(
    Names
)
VALUES
    ('Lucky Dube'), ('Abby Arnold') 


SELECT ArbSeq   = 1, *
FROM #Table1
UNION ALL
SELECT ArbSeq   = 2, *
FROM #Table2
ORDER BY ArbSeq

It should be noted that ordering is not guaranteed when not explicitly defined. If the table features a clustered index, the rows will typically be returned in the index's order - but this is not guaranteed.

应该注意的是，未明确定义时不保证排序。如果表具有聚簇索引，则通常会按索引的顺序返回行 - 但这不能保证。

#1