一个关于如何把查找结果存入中间表的问题?
CREATE TABLE T
(
工厂名称 VARCHAR(20),
产品号 VARCHAR(20),
数量 INT,
日期 DATETIME
)

INSERT INTO T
SELECT 'a','6800',51,'2007-5-14' UNION ALL
SELECT 'b','6800',52,'2007-5-14' UNION ALL
SELECT 'a','1920',53,'2007-5-14' UNION ALL
SELECT 'a','6800',54,'2007-5-15' UNION ALL
SELECT 'a','1920',54,'2007-5-15' UNION ALL
SELECT 'b','6800',54,'2007-5-15'
GO
select * from T
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT 日期=CASE WHEN GROUPING(日期) =1 THEN ''合计'' ELSE 日期 END'
SELECT @sql = @sql + ',['+产品号+'-'+工厂名称+']=ISNULL(SUM(CASE WHEN 工厂名称 = '''+工厂名称+''' AND
产品号 = '''+产品号+''' THEN 数量 END),0)'
FROM
( SELECT 产品号 FROM T GROUP BY 产品号 ) A, ( SELECT 工厂名称 FROM T GROUP BY 工厂名称 ) B
ORDER BY 产品号 DESC,工厂名称


EXEC( @sql + ',[合计]=SUM(数量) FROM (SELECT 工厂名称,产品号,数量,日期=CONVERT(CHAR(10),日期,120) FROM T) A GROUP BY 日期 WITH ROLLUP')

由于这个sql查询的结果是动态变化的,请问有方法把这个查询的结果存入一个中间表吗?






[解决办法]
EXEC(' @sql + ',[合计]=SUM(数量) into temptable FROM (SELECT 工厂名称,产品号,数量,日期=CONVERT(CHAR(10),日期,120) FROM T) A GROUP BY 日期 WITH ROLLUP ')



[解决办法]

SQL code

if object_id('中间表') is not null    exec('drop table 中间表')EXEC(@sql+',[合计]=SUM(数量) INTO [中间表] FROM (SELECT 工厂名称,产品号,数量,日期=CONVERT(CHAR(10),日期,120) FROM T) A GROUP BY 日期 WITH ROLLUP ')
[解决办法]
SQL code
--应该可以的啊,只不过不能使用临时表CREATE TABLE T(工厂名称 VARCHAR(20),产品号 VARCHAR(20),数量 INT,日期 DATETIME)INSERT INTO TSELECT 'a','6800',51,'2007-5-14' UNION ALLSELECT 'b','6800',52,'2007-5-14' UNION ALLSELECT 'a','1920',53,'2007-5-14' UNION ALLSELECT 'a','6800',54,'2007-5-15' UNION ALLSELECT 'a','1920',54,'2007-5-15' UNION ALLSELECT 'b','6800',54,'2007-5-15'GOselect * from TDECLARE @sql VARCHAR(8000)SET @sql='SELECT 日期=CASE WHEN GROUPING(日期)=1 THEN ''合计'' ELSE 日期 END'SELECT @sql=@sql+',['+产品号+'-'+工厂名称+']=ISNULL(SUM(CASE WHEN 工厂名称 ='''+工厂名称+''' AND 产品号='''+产品号+'''THEN 数量 END),0)'FROM(SELECT 产品号 FROM T GROUP BY 产品号)A,(SELECT 工厂名称 FROM T GROUP BY 工厂名称)BORDER BY 产品号 DESC,工厂名称EXEC(@sql+',[合计]=SUM(数量) INTO T1 FROM (SELECT 工厂名称,产品号,数量,日期=CONVERT(CHAR(10),日期,120) FROM T)A GROUP BY 日期 WITH ROLLUP')SELECT * FROM T1DROP TABLE T1DROP TABLE TCREATE TABLE T(工厂名称 VARCHAR(20),产品号 VARCHAR(20),数量 INT,日期 DATETIME)INSERT INTO TSELECT 'a','6800',51,'2007-5-14' UNION ALLSELECT 'b','6800',52,'2007-5-14' UNION ALLSELECT 'a','1920',53,'2007-5-14' UNION ALLSELECT 'a','6800',54,'2007-5-15' UNION ALLSELECT 'a','1920',54,'2007-5-15' UNION ALLSELECT 'b','6800',54,'2007-5-15'GOselect * from TDECLARE @sql VARCHAR(8000)SET @sql='SELECT 日期=CASE WHEN GROUPING(日期)=1 THEN ''合计'' ELSE 日期 END'SELECT @sql=@sql+',['+产品号+'-'+工厂名称+']=ISNULL(SUM(CASE WHEN 工厂名称 ='''+工厂名称+''' AND 产品号='''+产品号+'''THEN 数量 END),0)'FROM(SELECT 产品号 FROM T GROUP BY 产品号)A,(SELECT 工厂名称 FROM T GROUP BY 工厂名称)BORDER BY 产品号 DESC,工厂名称EXEC(@sql+',[合计]=SUM(数量) INTO T1 FROM (SELECT 工厂名称,产品号,数量,日期=CONVERT(CHAR(10),日期,120) FROM T)A GROUP BY 日期 WITH ROLLUP')SELECT * FROM T1DROP TABLE T1DROP TABLE T