MSSQL 2000函数分组合并求和

[sankyqiu]

MSSQL 2000 同货品、客户时数据合并 (在原函数的基础上修改)
货品 , 客户，客户品号，期初数量, 收入数量， 发出数量， 结存数量
GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 40 , 50 ,20
A1001 , C01 , C0101K , 0 , 60 , 10 ,50

A1001 , E22 , C0101D , 30 , 40 , 10 ,60

B2001 , K22 , C0101D , 60 , 40 , 10 ,90
B2001 , M22 , C0101D , 2 , 3 , 4 ,1

要求结果如下: (即当GOODSID=GOODSID AND DD1=DD1时取字段DD2内容中其中一个，其它数据需要求和。)
GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 100 , 60 ,70
A1001 , E22 , C0101D , 30 , 40 , 10 ,60
B2001 , K22 , C0101D , 60 , 40 , 10 ,90
B2001 , M22 , C0101D , 2 , 3 , 4 ,1

[sankyqiu]

以下是我正在使用的函数,麻烦帮忙在此基础上修改,或引用该函数创建新的函数,谢谢!
CREATE FUNCTION R_G_GOODSONHANDALL(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE (GOODSID INT, ST VARCHAR(40), GC VARCHAR(40),
GN VARCHAR(40), SPEC VARCHAR(80), UN VARCHAR(40),
GT VARCHAR(40),
GTC VARCHAR(20),
SQT FLOAT, IQT FLOAT,
OQT FLOAT, CQT FLOAT,
NBID INT, NB VARCHAR(60),
GRK1 VARCHAR(255), GRK2 VARCHAR(255), GRK3 VARCHAR(255)
)
AS
BEGIN
INSERT INTO @TempTable
SELECT G.GOODSID, STORE.NAME, G.CODE, G.NAME, G.SPEC,
GUNIT.NAME,
GDTYPE.NAME, GDTYPE.LCODE,
ISNULL(SUM(M.SQTY), 0), ISNULL(SUM(M.INQTY), 0), ISNULL(SUM(M.OUTQTY), 0),
0,
STORE.DEPSUBID, NB.NAME,
G.REMARK1, G.REMARK2, G.REMARK3
FROM R_G_GOODS_DETAIL_BYSTOREALL (@FROMDATE, @TODATE) M
LEFT JOIN GOODS G ON M.GOODSID = G.GOODSID
LEFT OUTER JOIN GOODSKIND ON GOODSKIND.GOODSKINDID = G.KIND
LEFT OUTER JOIN GDTYPE ON GDTYPE.GDTYPEID = G.GDTYPEID
LEFT OUTER JOIN STORE ON M.STOREID = STORE.STOREID
LEFT OUTER JOIN GOODSUNIT ON GOODSUNIT.GOODSID = G.GOODSID
LEFT OUTER JOIN GUNIT ON GUNIT.ID = GOODSUNIT.UNITID
LEFT OUTER JOIN D_SHOP NB ON STORE.DEPSUBID = NB.SHOPID
WHERE GOODSKINDID <= 3 AND ISBASEUNIT = 1 AND G.CLOSED=0
GROUP BY G.GOODSID, STORE.NAME, G.CODE, G.NAME, G.SPEC,
GUNIT.NAME,
GDTYPE.NAME, GDTYPE.LCODE,
STORE.DEPSUBID, NB.NAME,
G.REMARK1, G.REMARK2, G.REMARK3
UPDATE @TempTable SET CQT = SQT + IQT - OQT
RETURN
END

[快溜]

 select GOODSID,DD1,
        DD2=(select max(dd2) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        SQT=(select sum(SQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        IQT=(select sum(IQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        OQT=(select sum(OQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        CQT=(select sum(CQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1)
from tb a group by GOODSID,DD1

[javatemptation]

"当GOODSID=GOODSID AND DD1=DD1时取字段DD2内容中其中一个"

取哪一个？

[Shawn]

--1楼的函数，呵呵。只能说，不清楚。

create table #
(
	GOODSID varchar(100),
	DD1 varchar(100),
	DD2 varchar(100),
	SQT int,
	IQT int,
	OQT int,
	CQT int
)
insert #
select 'A1001', 'C01', 'C0101', '30', '40', '50', '20' union all
select 'A1001', 'C01', 'C0101K', '0', '60', '10', '50' union all
select 'A1001', 'E22', 'C0101D', '30', '40', '10', '60' union all
select 'B2001', 'K22', 'C0101D', '60', '40', '10', '90' union all
select 'B2001', 'M22', 'C0101D', '2', '3', '4', '1'

select * from
(select distinct GOODSID, DD1 from #) a
cross apply
(select top(1) DD2 from # where GOODSID = a.GOODSID and DD1 = a.DD1 order by DD2) b
cross apply
(select SQT = sum(SQT), IQT = sum(IQT), OQT = sum(OQT), CQT = sum(CQT) from # where GOODSID = a.GOODSID and DD1 = a.DD1) c

[sankyqiu]

GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 100 , 60 ,70

GOODSID和DD1同时相同时取字段DD2里面的内容之一

[sankyqiu]

要求结果如下: (即当GOODSID=GOODSID AND DD1=DD1时取字段DD2内容中其中一个，其它数据需要求和。)
GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 100 , 60 ,70 ----请注意这里合并了数据

[javatemptation]

[Quote=引用 6 楼 sankyqiu 的回复:]
要求结果如下: (即当GOODSID=GOODSID AND DD1=DD1时取字段DD2内容中其中一个，其它数据需要求和。)
GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 100 , 60 ,70 ----请注意这里合并了数据
[/Quote]


这里DD2的值为什么是"c0101"，"C0101K"也可以啊，你说的取内容中的其中一个啊

[sankyqiu]

[Quote=引用 7 楼 lf19820717 的回复:]
引用 6 楼 sankyqiu 的回复:
要求结果如下: (即当GOODSID=GOODSID AND DD1=DD1时取字段DD2内容中其中一个，其它数据需要求和。)
GOODSID , DD1 , DD2 , SQT , IQT , OQT ,CQT
A1001 , C01 , C0101 , 30 , 100 , 60 ,70 ----请注意这里合并了数据



这里DD2的值……
[/Quote]

是对,正确!

[wing7742]

只要结果的话 这样也行

SELECT goodsid,dd1,dd2=min(dd2),sqt=SUM(sqt),iqt=SUM(iqt),oqt=SUM(oqt),cqt=SUM(cqt) 
FROM # GROUP BY goodsid,dd1

/*

goodsid    dd1        dd2        sqt         iqt         oqt         cqt
---------- ---------- ---------- ----------- ----------- ----------- -----------
A1001      C01        C0101      30          100         60          70
A1001      E22        C0101D     30          40          10          60
B2001      K22        C0101D     60          40          10          90
B2001      M22        C0101D     2           3           4           1


*/

[javatemptation]

select GOODSID,DD1,
        DD2=(select max(dd2) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        SQT=(select sum(SQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        IQT=(select sum(IQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        OQT=(select sum(OQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1),
        CQT=(select sum(CQT) from tb where GOODSID=a.GOODSID and dd1=a.dd1)
from tb a group by GOODSID,DD1