select B, sum(D) D,sum(E) E,sum(F) F from tb where G between '2008-10-4' and '2008-10-6' group by B然后对上面这个查询进行子查询的操作即可. 例如 select b , d-e , d-f , d- f from ( select B, sum(D) D,sum(E) E,sum(F) F from tb where G between '2008-10-4' and '2008-10-6' group by B ) t也可以直接 select B, sum(D-E) 'D-E',sum(E-F) 'E-F',sum(D-F) 'D-F' from tb where G between '2008-10-4' and '2008-10-6' group by B
例如
select b , d-e , d-f , d- f from
(
select B, sum(D) D,sum(E) E,sum(F) F from tb where G between '2008-10-4' and '2008-10-6' group by B
) t也可以直接
select B, sum(D-E) 'D-E',sum(E-F) 'E-F',sum(D-F) 'D-F' from tb where G between '2008-10-4' and '2008-10-6' group by B
XXY-CCN应该为123 111 142 12
insert into tb values(1 , 'XXY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(2 , 'XXY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(5 , 'XXY-CCN' , 369 , 333 , 436 , 36 , '2008-10-4')
insert into tb values(6 , 'XXY-CCN' , 492 , 444 , 582 , 48 , '2008-10-6')
insert into tb values(8 , 'XBY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(9 , 'XBY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(11, 'XBY-CCN' , 369 , 333 , 436 , 36 , '2008-10-5')
goselect B, sum(D-E-F) 'D-E-F' from tb where G between '2008-10-4' and '2008-10-6' group by Bdrop table tb/*
B D-E-F
---------- -----------
XBY-CCN -139
XXY-CCN -325(所影响的行数为 2 行)
*/
insert into tb values(1 , 'XXY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(2 , 'XXY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(5 , 'XXY-CCN' , 369 , 333 , 436 , 36 , '2008-10-4')
insert into tb values(6 , 'XXY-CCN' , 492 , 444 , 582 , 48 , '2008-10-6')
insert into tb values(8 , 'XBY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(9 , 'XBY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(11, 'XBY-CCN' , 369 , 333 , 436 , 36 , '2008-10-5')
goselect B, sum(D-E-F) 'D-E-F' from tb where G between '2008-10-4' and '2008-10-6' group by B
/*
B D-E-F
---------- -----------
XBY-CCN -139
XXY-CCN -325(所影响的行数为 2 行)
*/select B, D-E-F 'D-E-F' from tb where G between '2008-10-4' and '2008-10-6'
/*
B D-E-F
---------- -----------
XXY-CCN -139
XXY-CCN -186
XBY-CCN -139(所影响的行数为 3 行)
*/drop table tb
insert into tb values(1 , 'XXY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(2 , 'XXY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(5 , 'XXY-CCN' , 369 , 333 , 436 , 36 , '2008-10-4')
insert into tb values(6 , 'XXY-CCN' , 492 , 444 , 582 , 48 , '2008-10-6')
insert into tb values(8 , 'XBY-CCN' , 123 , 111 , 142 , 12 , '2008-10-1')
insert into tb values(9 , 'XBY-CCN' , 246 , 222 , 284 , 24 , '2008-10-2')
insert into tb values(11, 'XBY-CCN' , 369 , 333 , 436 , 36 , '2008-10-5')
goSELECT IDENTITY(INT,1,1) as Row,* INTO #tmp FROM tb
---WHERE G Between '2008-10-4' AND '2008-10-06'
ORDER BY ASELECT t.B,
SUM(t.C-ISNULL(v.C,0)) ,
SUM(t.D-ISNULL(v.D,0)),
SUM(t.E-ISNULL(v.E,0)),
SUM(t.F-ISNULL(v.F,0))
FROM #tmp t LEFT JOIN #tmp v
ON t.B=v.B AND t.Row=v.Row+1
GROUP BY t.Bselect * from #tmp t LEFT JOIN #tmp v ON t.Row=v.Row+1