asp 求多列和

[tangh2177]

msSql数据库中有表如下

表1
id indate
121 2005
122 2006
123 2007
124 2008
125 2009

表2
id2 col1 col2 clos3 clos4
121 11 12 13 14
122 10 11 10 15
123 1 2 30 1
124 21 22 20 24
125 31 32 33 34

结果： =32 =35 =60 =40

根据这两表，我想求 2006年和2008年（含2006，2008）之间的col1列，col2列，clos3列,clos4列的和。
如 结果那样。请教如何做。

[lihan6415151528]

CREATE TABLE TB1(ID INT,INDATE INT)
INSERT INTO TB1 
SELECT 121,2005 UNION ALL
SELECT 122,2006 UNION ALL
SELECT 123,2007 UNION ALL
SELECT 124,2008 UNION ALL
SELECT 125,2009 
GO

CREATE TABLE TB2(ID INT,COL1 INT,COL2 INT,COL3 INT,COL4 INT)
INSERT INTO TB2
SELECT 121,11,12,13,14 UNION ALL 
SELECT 122,10,11,10,15 UNION ALL
SELECT 123,1,2,30,1 UNION ALL  
SELECT 124,21,22,20,24 UNION ALL 
SELECT 125,31,32,33,34
GO

SELECT * FROM TB1;
SELECT * FROM TB2;

SELECT SUM(COL1)[SUM1],SUM(COL2)[SUM2],SUM(COL3)[SUM3],SUM(COL4)[SUM4]
FROM TB2
WHERE TB2.ID>(SELECT ID FROM TB1 WHERE INDATE = 2005) 
AND
TB2.ID<(SELECT ID FROM TB1 WHERE INDATE = 2009)

SUM1        SUM2        SUM3        SUM4        
----------- ----------- ----------- ----------- 
32          35          60          40

（所影响的行数为 1 行）

[lzp4881]

select sum(col1),sum(col2),sum(col3),sum(col4) from 表2
where id2 in (select id from 表1 where indate>2005 and indate<2009)