如何创建临时表，添加其他表中的数据和总计 [英] How do I create temporary tables, add data from other tables and total

问题描述

DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))

INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD

Go

SELECT Table2.col_10, Table2.col_11, Table2.col_12

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12

Go

SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)

FROM dbo.Table1

UPDATE @RevTotalstbl SET PeriodTTL = sumCurPerAdj, YTDTTL = sumFYDamt, JTDTTL = sumITDamt

[edit]已修复代码块-OriginalGriff [/edit]

[edit]Code Block fixed - OriginalGriff[/edit]

推荐答案

1.您将@Totaltbl声明为表变量(表变量！= temp table)，任何变量都只能在已创建的批处理中访问.
2.您更新从未声明的表@RevTotalstbl

3.代码应类似于

1. You declare @Totaltbl as table variable (table variable != temp table ) and any variable can be accessed only in the batch that was created.

2. You update table @RevTotalstbl that is never declared

3. The code should look like

DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))
INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD
--Go
SELECT Table2.col_10, Table2.col_11, Table2.col_12
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12
--Go
DECLARE @RevTotalstbl TABLE( COLUMN DEFINITIONS GO HERE)
SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)
FROM dbo.Table1
UPDATE @RevTotalstbl SET PeriodTTL = sumCurPerAdj, YTDTTL = sumFYDamt, JTDTTL = sumITDamt

这篇关于如何创建临时表，添加其他表中的数据和总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！