如何将一些虚拟值添加到现有的 T-SQL 查询中?

Question

我正在使用SQL Server 2014，我有以下T-SQL查询：

USE MyDatabase
SELECT [Property]
,[Pax]
,[Mth]
,'FY1718' as [ID]
FROM Table1
WHERE [Mth] between '2017-07-01' and '2018-06-01'
UNION ALL
SELECT [Property]
,[Pax]
,[Mth]
,'FY1819' as [ID]
FROM Table2
WHERE [Mth] between '2018-07-01' and '2019-06-01'

输出的摘录：

Property    Pax   Mth           ID
TRO        120   2017-07-01    FY1718
EBC        95    2018-08-01    FY1819

我想使用以下值创建 2 个虚拟条目：

EBC,  0,  2017-09-01, FY1718
GHY,  0,  2017-10-01, FY1718

我的最终输出应如下所示：

Property   Pax   Mth           ID
TRO        120   2017-07-01    FY1718
EBC        95    2018-08-01    FY1819
EBC        0     2017-09-01    FY1718
GHY        0     2017-10-01    FY1718

如何在最终输出中添加这些行？

Answer 1

使用 UNION ALL

SELECT [Property]
,[Pax]
,[Mth]
,'FY1718' as [ID]
FROM Table1
WHERE [Mth] between '2017-07-01' and '2018-06-01'
UNION ALL
SELECT [Property]
,[Pax]
,[Mth]
,'FY1819' as [ID]
FROM Table2
WHERE [Mth] between '2018-07-01' and '2019-06-01'
union all
SELECT 'EBC'
,0
,'2017-09-01'
,'FY1819'
union all
SELECT 'GHY'
,0
,'2017-10-01'
,'FY1819'

Answer 2

SELECT [Property],
[Pax],
[Mth],
'FY1718' AS [ID] FROM   Table1 WHERE  [Mth] BETWEEN '2017-07-01' AND '2018-06-01'
UNION ALL
SELECT [Property],
[Pax],
[Mth],
'FY1819' AS [ID]
FROM   Table2
WHERE  [Mth] BETWEEN '2018-07-01' AND '2019-06-01'
UNION ALL       
SELECT *
FROM   (
VALUES ('EBC', 0, '2017-09-01', 'FY1818'),
('GHY', 0, '2017-10-01', 'FY1818')
) DummyTable([Property], [Pax], [Mth], [ID])