MSSQL 2000函数分组合并求和

sankyqiu 2011-02-26 10:45:16

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

...全文

119 点赞收藏 10

写回复

10 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

javatemptation 2011-02-27



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

回复赞

wing7742 2011-02-27



只要结果的话 这样也行



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





*/

回复赞

sankyqiu 2011-02-27

[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]

是对,正确!

回复赞

javatemptation 2011-02-27

[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 2011-02-26

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

回复赞

sankyqiu 2011-02-26

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

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

回复赞

Shawn 2011-02-26

--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

回复赞

javatemptation 2011-02-26

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

取哪一个？

回复赞

快溜 2011-02-26

 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

回复赞

sankyqiu 2011-02-26

以下是我正在使用的函数,麻烦帮忙在此基础上修改,或引用该函数创建新的函数,谢谢!
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

回复赞