将所有SQL创建表语句打包成单个语句

Question

为了生成我的最终表CCtbase2，我完成了编写多个SQL语句。接下来我想做的是将整个SQL代码打包到单个SQL语句中，以避免创建多个临时表。我只是想以一种方式写我的查询，我将在不创建多个临时表的情况下创建我的最终表，但我不确定如何去做，我为没有提供表的样本而道歉。

DROP TABLE IF EXISTS temp_t1;
Select PID, CID ,CCID, CExpiry, PStatusID, PDate into temp_t1 
FROM  TABLE1 where PStatusID = 1001;
-- Applying filters
DROP TABLE IF EXISTS temp_t2;
SELECT * into temp_t2 
FROM TABLE2 where ADate < GETDATE() and PTID > 0 and  PTStatusID <> 812 ;
-- Fetching the latest record as per ADate based on PTID
DROP TABLE IF EXISTS temp_t3;
Select * into temp_t3 from (
select *,  row_number() over(partition by PTID order by ADate desc) as rn
from temp_t2
) t
where t.rn = 1 
DROP TABLE IF EXISTS temp_t4;
SELECT *,
CASE WHEN TTID = 2301 and PTStatusID in (800,801) THEN PAmount
ELSE 0 END AS OAmount
into temp_t4 from temp_t3
DROP TABLE IF EXISTS temp_t5
Select PID, sum(OAmount) as Final_amt into temp_t5
from  temp_t4  group by PID; 
DROP TABLE IF EXISTS CCtbase1
Select * into CCtbase1  from temp_t1 where PID not in 
(Select distinct(PID) from temp_t5 where Final_amt = 0);
DROP TABLE IF EXISTS CCtbase2
Select a.*, b.EDate, c.EID into CCtbase2 from CCtbase1 a 
left join  TABLE3 b on a.CCID = b.CCID  
left join  TABLE4 c on a.CID = c.CID;

Answer 1

由于没有提供样例数据，我想出了这个:

WITH T1_CTE as (
SELECT PID, CID ,CCID, CExpiry, PStatusID, PDate
FROM  TABLE1 where PStatusID = 1001
),
T2_CTE as (
SELECT *, CASE WHEN TTID = 2301 AND PTStatusID IN (800,801) THEN PAmount ELSE 0 END AS OAmount
FROM
(
SELECT *, row_number() OVER(PARTITION BY PTID ORDER BY ADate DESC) rowNum
FROM TABLE2
WHERE ADate < GETDATE() AND PTID > 0 AND  PTStatusID <> 812
) t
WHERE t.rowNum = 1
),
T2_F_CTE as (
SELECT PID, SUM(OAmount) as Final_amt
FROM T2_CTE
GROUP BY PID
)
SELECT a.*, b.EDate, c.EID
FROM T1_CTE a
LEFT JOIN TABLE3 b ON a.CCID = b.CCID
LEFT JOIN TABLE4 c ON a.CID = c.CID
WHERE PID NOT IN (SELECT PID from T2_F_CTE where Final_amt = 0)

这将防止您创建过多的临时表。

Answer 2

你能试试这个解决方案吗

CREATE TABLE TABLE1(
PID int, 
CID int,
CCID int, 
CExpiry datetime, 
PStatusID int, 
PDate datetime
)
CREATE TABLE TABLE2(
PID int, 
ADate DATETIME,
PTID int,
PTStatusID int, 
TTID int, 
PAmount int
)
DROP TABLE IF EXISTS CCtbase1
;with TABLE2CTE(PID,OAmount) as  
(  
SELECT TOP 1 
PID, CASE 
WHEN TTID = 2301 AND PTStatusID in (800,801) THEN PAmount
ELSE 0 
END AS OAmount 
FROM TABLE2 
WHERE 
ADate < GETDATE() 
AND PTID > 0 
AND TTID = 2301 
AND PTStatusID in (800,801) 
ORDER BY ADate DESC
)  
SELECT 
PID, 
CID, 
CCID, 
CExpiry, 
PStatusID, 
PDate 
INTO CCtbase1 
FROM TABLE1 
WHERE 
PID NOT IN 
(Select distinct(PID) from TABLE2CTE where OAmount = 0)