insert into table1(AA,BB,CC,DD,EE,FF,GG)
SELECT 'C','CK001',200910, a.XX, B.yy, c.cCOUNTITEM,d.dCOUNTITEM
FROM table2 a, table3 b,
(select sum(COUNTITEM) AS cCOUNTITEM from table4 where Type=1) as c,
(select sum(COUNTITEM) AS dCOUNTITEM from table4 where Type=2,'aaa') AS d
where a.ID='CC' and b.ID='ff'
两个Sum语句各加一个判断,如果为null则值为0如果不为null则就用sum计算出来的值 ???
SELECT 'C','CK001',200910, a.XX, B.yy,
isnull(c.cCOUNTITEM,0),isnull(d.dCOUNTITEM,0)
FROM table2 a, table3 b,
(select sum(COUNTITEM) AS cCOUNTITEM from table4 where Type=1) as c,
(select sum(COUNTITEM) AS dCOUNTITEM from table4 where Type=2,'aaa') AS d
where a.ID='CC' and b.ID='ff'
SELECT 'C','CK001',200910, a.XX, B.yy, c.cCOUNTITEM,d.dCOUNTITEM
FROM table2 a, table3 b,
isnull((select sum(COUNTITEM) AS cCOUNTITEM from table4 where Type=1),0) as c,
isnull((select sum(COUNTITEM) AS dCOUNTITEM from table4 where Type=2,'aaa'),0) AS d
where a.ID='CC' and b.ID='ff'
SELECT 'C','CK001',200910, a.XX, B.yy, c.cCOUNTITEM,d.dCOUNTITEM
FROM table2 a, table3 b,
(select sum(isnull(COUNTITEM,0)) AS cCOUNTITEM from table4 where Type=1) as c,
(select sum(isnull(COUNTITEM,0)) AS dCOUNTITEM from table4 where Type=2,'aaa') AS d
where a.ID='CC' and b.ID='ff'
INSERT INTO TABLE1(AA,BB,CC,DD,EE,FF,GG)
SELECT 'C','CK001',200910, A.XX, B.YY,
ISNULL((SELECT SUM(COUNTITEM) AS CCOUNTITEM FROM TABLE4 WHERE TYPE=1),0),
ISNULL((SELECT SUM(COUNTITEM) AS DCOUNTITEM FROM TABLE4 WHERE TYPE=2),0)
FROM TABLE2 A, TABLE3 B
WHERE A.ID='CC' AND B.ID='FF'
SELECT 'C','CK001',200910, a.XX, B.yy, isnull(c.cCOUNTITEM,0),isnull(d.dCOUNTITEM,0)
FROM table2 a, table3 b,
select sum(COUNTITEM) AS cCOUNTITEM from table4 where Type=1) as c,
select sum(COUNTITEM) AS dCOUNTITEM from table4 where Type=2,'aaa') AS d
where a.ID='CC' and b.ID='ff'
谢谢水哥提醒不过楼主的也有点问题
insert into table1(AA,BB,CC,DD,EE,FF,GG)
SELECT 'C','CK001',200910, a.XX, B.yy, c.cCOUNTITEM,d.dCOUNTITEM
FROM table2 a, table3 b,
(select sum(isnull(COUNTITEM,0)) AS cCOUNTITEM from table4 where Type=1) as c,
(select sum(isnull(COUNTITEM,0)) AS dCOUNTITEM from table4 where Type=2) AS d
where a.ID='CC' and b.ID='ff'
意思应该是先对表中的数据进行判断吧,如果数据是NULL就置0
是这个吗?
如果是的。insert into table1(AA,BB,CC,DD,EE,FF,GG)
SELECT 'C','CK001',200910, a.XX, B.yy, c.cCOUNTITEM,d.dCOUNTITEM
FROM table2 a, table3 b,
(select sum(COUNTITEM) AS cCOUNTITEM from (select case COUNTITEM when null then 0 case else COUNTITEM From table4)temp where Type=1) as c,
(select sum(COUNTITEM) AS dCOUNTITEM from (select case COUNTITEM when null then 0 case else COUNTITEM From table4)temp where Type=2,'aaa') AS d
where a.ID='CC' and b.ID='ff'