用现有查询连接临时表

Question

我在SSMS中有2个查询，执行得很好。我需要将它们连接在一起，但我不确定如何(我是T-SQL的初学者)。

第一个脚本是从数据库中提取数据并转换一些列来创建我的事实表:

SELECT 'Policy' As PolicyType,  
PolicyKey = [POLICY_NUMBER] + ' ' + CONVERT(VARCHAR(11), [TRNS_MSG_TS], 120),  
"BATCH_ID",   
"HISTORY_ID",   
"POLICY_NUMBER",  
"TRANSACTION_TYPE_CODE",  
"POLICY_STATUS",  
"COVER_WANTED",  
"PACKAGE_TYPE",  
"MIGRATION_INDICATOR",  
"REFERRER_PREFIX",  
"PROCESSOR_PREFIX",  
"INTRODUCER_CHANNEL",  
"INTRODUCER_SUBCHANNEL",  
CAST("POLICY_VERSION_EFFECTIVE_START_DATE" AS DATE) As StartDate,  
CAST("POLICY_EXPIRY_DATE" AS DATE) As PQExpiryDate,  
CAST("CANCELLATION_DATE" AS DATE) As CancellationDate,  
"FINALISED_PREMIUM_AMOUNT",  
"ANNUAL_PREMIUM",  
"TRANSACTIONAL_PREMIUM",  
CAST("TRNS_MSG_TS" AS DATE) As TRNSDate,  
CAST(LOADDATE AS DATE) As LoadDate,  
OFFSET_IND,  
FIX_IND  
FROM Database.dbo."View1"  
WHERE "POLICY_VERSION_EFFECTIVE_START_DATE" >= DATEADD(DAY,-1*DAY(GETDATE())+0,         DATEADD(MONTH,-12,GETDATE()))  
AND OFFSET_IND = 0  
AND FIX_IND = 0  
AND LOADDATE <= CAST(GETDATE() AS DATE)  

第二个脚本是创建一个临时表，我想用它作为映射表:

SELECT DISTINCT "POLICY_NUMBER",  
'Policy' as PolicyType,  
CAST(MIN("TRNS_MSG_TS") As Date) As MinDate  
into #FirstTransFlagMap  
FROM Database.dbo."View1"  
Group by "POLICY_NUMBER";  
SELECT PolicyKey = ["POLICY_NUMBER"] + ' ' + CONVERT(VARCHAR(11),[MinDate], 120),  
'1' as FirstTransFlag  
FROM #FirstTransFlagMap;  

我试图将这两个脚本连接在一起，以便事实表输出上述所有列，以及值为1(如果PolicyKeys匹配)和0(如果不匹配)的FirstTransFlag列

谁能帮我把这两个脚本一起得到想要的结果?

我只是不知道如何格式化我的语法来加入这些脚本。第一次尝试使用临时表/字段，第一次尝试在SSMS中将表连接在一起

Answer 1

既然已经创建了临时表，就可以按原样使用它了。对于第一个查询，可以使用CTE(公共表表达式)。

所以你会有像

这样的东西

WITH FirstQueryTabularDataCTE (all properties that you need)
AS
(
FirstQuery
)
SELECT f.Property1 from #FirstTransFlagMap AS f
JOIN FirstQueryTabularDataCTE AS CTE
ON f.Id = CTE.Id

显然是伪代码，但应该引导您往正确的方向。