sql server中的临时表

问题描述

您何时在sql中使用临时表，请在更新和删除时给我一个示例

推荐答案

DECLARE @WorkCode VARCHAR(25)
SET @WorkCode='61163201328'

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp(WorkId VARCHAR(50),TLabourAmt decimal(18,2),TMaterialAmt decimal(18,2)
)

DELETE FROM #Temp

INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=1
GROUP BY WME.workID

INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=2
GROUP BY WME.workID

INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=3
GROUP BY WME.workID

INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=4
GROUP BY WME.workID

SELECT * FROM #Temp order by WorkId

这里我使用临时表进行插入基于measurementCount

Here i am using temp table for inserting based on measurementCount

这篇关于sql server中的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！