34,590
社区成员
发帖
与我相关
我的任务
分享
--new data:
CREATE TABLE TEST (A VARCHAR(10),B VARCHAR(10),C VARCHAR(10),D VARCHAR(10),E FLOAT)
GO
INSERT INTO TEST
SELECT '001','2009-1-1','aaa','2009-1-1',898.89 UNION ALL
SELECT '001','2009-1-1','aaa','2009-1-1',45.45 UNION ALL
SELECT '001','2009-1-1','aaa','2009-1-1',6789.39 UNION ALL
SELECT '001','2009-1-1','aaa','2009-1-1',5000 UNION ALL
SELECT '002','2009-1-1','aaa','2009-1-1',10 UNION ALL
SELECT '002','2009-1-1','aaa','2009-1-1',8.89 UNION ALL
SELECT '002','2009-1-1','aaa','2009-1-1',9090.9
GO
(7 行受影响)
select
A.A,A.B,A.C,A.D,SUM(A.E)
from
TEST a
GROUP BY
A.A,A.B,A.C,A.D
--result:
A B C D
---------- ---------- ---------- ---------- ----------------------
001 2009-1-1 aaa 2009-1-1 12733.73
002 2009-1-1 aaa 2009-1-1 9109.79
(2 行受影响)
--alter data:
INSERT INTO TEST
SELECT '001','2009-2-1','bbb','2009-2-1',5000 UNION ALL
SELECT '002','2009-2-1','bbb','2009-2-1',10
(2 行受影响)
--方式二:
select
A,MAX(B) B,MAX(C) C,MAX(D) D,SUM(E) E
from
TEST
GROUP BY
A
--result:
A B C D E
---------- ---------- ---------- ---------- ----------------------
001 2009-2-1 bbb 2009-2-1 17733.73
002 2009-2-1 bbb 2009-2-1 9119.79
(2 行受影响)