Sql插入#temp表 [英] Sql insert #temp table

问题描述

CREATE TABLE #TEMP (Date  NVARCHAR(256),Pending NVARCHAR(256),Complete NVARCHAR(256) ,Total  NVARCHAR(256));
INSERT INTO #TEMP (Date,Pending,Total) 
SELECT DISTINCT(DATEE),'','' from E_R_MainT
GROUP BY DATEE

UPDATE #TEMP
SET Pending=(SELECT COUNT(R_ID) FROM E_R_MainT WHERE STATUS !='S_2' GROUP BY (CONVERT(char(10), HOD_DT ,126)) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY DATEE)

UPDATE #TEMP
SET Complete=(SELECT COUNT(R_ID) FROM E_R_MainT WHERE STATus !='S_1' GROUP BY DATEE )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY DATEE )

UPDATE #TEMP
SET Total=(SELECT COUNT(R_ID) FROM E_R_MainT  GROUP BY (DATEE) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY  DATEE
SELECT * FROM #TEMP

上面的查询是我的查询，使用SELECT

语句插入临时表。







以下是我的桌子



R_ID DATEE状态

1001 2016-08-15 s_1

1002 2016-08-12 s_2

1003 2016-08-12 s_2

1004 2016-08- 15 2_2

1005 2016-08-12 s_1

1006 2016-08-15 s_2

1007 2016-08-11 s_1



* s_1 =待定，* s_2 =完成

i期望获得如下输出：



日期待定完成总额

2016-08-11 1 0 1

2016-08-12 1 2 3

2016-08 -15 1 2 3



我尝试过：



子查询返回超过1的值。这是不允许的，我在执行查询时遇到此错误

query above are my query that insert temporary table using SELECT
statement.



BELOW is MY TABLE

R_ID DATEE STATUS
1001 2016-08-15 s_1
1002 2016-08-12 s_2
1003 2016-08-12 s_2
1004 2016-08-15 2_2
1005 2016-08-12 s_1
1006 2016-08-15 s_2
1007 2016-08-11 s_1

*s_1 = pending,*s_2 = complete
i expect to get output like this :

Date Pending Complete Total
2016-08-11 1 0 1
2016-08-12 1 2 3
2016-08-15 1 2 3

What I have tried:

Subquery returned more than 1 value. This is not permitted ,I get this error when execute the query

推荐答案

我会创建一个词典表格！



试试这个：

I'd create a "dictionary" table!

Try this:

DECLARE @tmp TABLE(OldStatus, NewStatus)
INSERT INTO @tmp(OldStatus, NewStatus)
VALUES('s_1', 'Pending'), ('s_1', 'Complete')

SELECT Date, [Pending], [Complete], [Pending] + [Complete] AS  [Total]
FROM (
    SELECT A.Date, B.NewStatus AS Status 
    FROM #Temp AS A INNER JOIN @tmp AS B ON A.Status = B.OldStatus
) AS DT
PIVOT(COUNT(Date) FOR Status IN([Pending], [Complete], [Total])) AS PV

这篇关于Sql插入#temp表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！