有没有其他方法可以在不使用临时表的情况下编写这些查询？

我有3个表（tb_cdcp_ATQchild，tb_cdcp_ATQInfant，tb_cdcp_ATQPreschool），看起来与下面的第一个表相似。根据IndividualID和createdate，我需要计算第二个数字低于第一个数字的所有IndividualID。例如，对于IndividualID = 7029AF536A9EB4B1697D，如果您将C_Q1到C_Q5进行计数，则1/18/19的总数为9，如果您将C_Q1到C_Q5进行计数，则1/19/19的总数为5，因此，IndividualID的计数为1，因为第二个数字比第一个数字小。我有3个表，看起来就像具有相同的逻辑。第四张表-tb_cdcp_ANSchecklist-下方的第二张表-具有True和False，需要与其他3个表包括在一起，但逻辑略有不同。因此，对于tb_cdcp_ANSchecklist表中的7029AF536A9EB4B1697D，

tb_cdcp_ATQchild

tb_cdcp_ANS检查清单

---Child Tables
IF OBJECT_ID('tempdb.dbo.#temp1', 'U') IS NOT NULL
  DROP TABLE #temp1; 
select *,Row_number() Over(Partition by ATQChild_IndividualID Order by ATQChild_CreateDate) as rownum
into #temp1
from [dbo].[vCDCP_rptChildImprove]

IF OBJECT_ID('tempdb.dbo.#temp2', 'U') IS NOT NULL
  DROP TABLE #temp2; 
select a.ATQChild_IndividualID,a.ATQChild_CreateDate,a.ChildSum,b.ChildSum as B_Childsum,case when a.ChildSum<b.ChildSum then 1 else 0 end as flag
into #temp2
from #temp1 a left join #temp1 b on a.ATQChild_IndividualID=b.ATQChild_IndividualID and a.rownum=b.rownum+1

IF OBJECT_ID('tempdb.dbo.#temp3', 'U') IS NOT NULL
  DROP TABLE #temp3; 
select *,row_number() over(Partition by ATQChild_IndividualID,flag order by ATQChild_CreateDate) as rownum1
into #temp3
from #temp2

UPDATE #temp3
SET flag=0
where flag=1 and rownum1>1

---Infant Tables
IF OBJECT_ID('tempdb.dbo.#temp4', 'U') IS NOT NULL
  DROP TABLE #temp4; 
select *,Row_number() Over(Partition by ATQInfant_IndividualID Order by ATQInfant_CreateDate) as rownum
into #temp4
from [dbo].[vCDCP_rptInfantImprove]

IF OBJECT_ID('tempdb.dbo.#temp5', 'U') IS NOT NULL
  DROP TABLE #temp5; 
select a.ATQInfant_IndividualID,a.ATQInfant_CreateDate,a.InfantSum,b.InfantSum as B_Infantsum,case when a.InfantSum<b.InfantSum then 1 else 0 end as flag
into #temp5
from #temp4 a left join #temp4 b on a.ATQInfant_IndividualID=b.ATQInfant_IndividualID and a.rownum=b.rownum+1

IF OBJECT_ID('tempdb.dbo.#temp6', 'U') IS NOT NULL
  DROP TABLE #temp6; 
select *,row_number() over(Partition by ATQInfant_IndividualID,flag order by ATQInfant_CreateDate) as rownum1
into #temp6
from #temp5

UPDATE #temp6
SET flag=0
where flag=1 and rownum1>1

---Preschool Tables
IF OBJECT_ID('tempdb.dbo.#temp7', 'U') IS NOT NULL
  DROP TABLE #temp7; 
select *,Row_number() Over(Partition by ATQPreschool_IndividualID Order by ATQPreschool_CreateDate) as rownum
into #temp7
from [dbo].[vCDCP_rptPreschoolImprove]

IF OBJECT_ID('tempdb.dbo.#temp8', 'U') IS NOT NULL
  DROP TABLE #temp8; 
select a.ATQPreschool_IndividualID,a.ATQPreschool_CreateDate,a.PreschoolSum,b.PreschoolSum as B_Preschoolsum,case when a.PreschoolSum<b.PreschoolSum then 1 else 0 end as flag
into #temp8
from #temp7 a left join #temp7 b on a.ATQPreschool_IndividualID=b.ATQPreschool_IndividualID and a.rownum=b.rownum+1

IF OBJECT_ID('tempdb.dbo.#temp9', 'U') IS NOT NULL
  DROP TABLE #temp9; 
select *,row_number() over(Partition by ATQPreschool_IndividualID,flag order by ATQPreschool_CreateDate) as rownum1
into #temp9
from #temp8

UPDATE #temp9
SET flag=0
where flag=1 and rownum1>1

---ANS Tables
IF OBJECT_ID('tempdb.dbo.#temp10', 'U') IS NOT NULL
  DROP TABLE #temp10; 
select *,Row_number() Over(Partition by ANS_IndividualID Order by ANS_CreateDate) as rownum
into #temp10
from [dbo].[vCDCP_rptANSImprove]

IF OBJECT_ID('tempdb.dbo.#temp11', 'U') IS NOT NULL
  DROP TABLE #temp11; 
select a.ANS_IndividualID,a.ANS_CreateDate,a.ANS_DepSum as A_DepSum,b.ANS_DepSum as B_DepSum,a.ANS_ArrSum as A_ArrSum, b.ANS_ArrSum as B_ArrSum,
case when a.ANS_DepSum<b.ANS_DepSum then 1 when a.ANS_ArrSum<b.ANS_ArrSum then 1 else 0 end as flag
into #temp11
from #temp10 a left join #temp10 b on a.ANS_IndividualID=b.ANS_IndividualID and a.rownum=b.rownum+1

IF OBJECT_ID('tempdb.dbo.#temp12', 'U') IS NOT NULL
  DROP TABLE #temp12; 
select *,row_number() over(Partition by ANS_IndividualID,flag order by ANS_CreateDate) as rownum1
into #temp12
from #temp11

UPDATE #temp12
SET flag=0
where flag=1 and rownum1>1