Server组合结果集

Question

我试图将两个不同查询的结果集组合成一个结果集。如果不使用存储过程(可能使用WITH关键字)，这是可能的吗？

以下列情况为例：

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

请忽略建议OR的明显解决方案，因为我忽略了复杂的动态约束，只是为了说明我的观点。

我想将每个查询的结果分别，并将它们合并到一个结果集中。

Answer 1

您可以在这两个查询之间使用UNION ALL：

SELECT *, '2013' Source
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *, '2014' Source
FROM student 
WHERE graduation_year = 2014 

请参阅与Demo

我添加了一个名为source的字段，如果您需要标识该行来自哪个数据集，则如果不需要，可以排除该字段。

如果您想在临时表中这样做，可以使用以下内容：

select *
into #temp
from
(
  SELECT *, '2013' Source
  FROM student 
  WHERE graduation_year = 2013 
  UNION ALL
  SELECT *, '2014' Source
  FROM student 
  WHERE graduation_year = 2014 
) src


select * from #temp

请参阅与Demo

Answer 2

您可以使用UNION ALL

SELECT *
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *
FROM student 
WHERE graduation_year = 2014 

Answer 3

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union All -- Retrieves all results from both recordsets including both duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union -- Retrieves all results from both recordsets discarding duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows