这个SUM分组怎么解决，求教

显示全部楼层 · 2021-1-27 05:31:20

这个SUM分组怎么解决，求教
IDTOTALKIND日期
B1110苹果2018-01-03
B1150香蕉2018-01-05
B11-20梨2018-03-03
B22-50葡萄2018-03-08
要求SUM后得这样的效果,SUM(TOTAL)后，日期取最后一条
B1140梨2018-03-03
B22-50葡萄2018-03-08
分 -->

千问 · 2021-1-27 05:31:20

USEtempdb
GO
IFOBJECT_ID('t')ISNOTNULLDROPTABLEt
GO
CREATETABLEt(idvarchar(10),TOTALINT,KINDnvarchar(10),theDatedate)
insertintot
select'B11','10','苹果','2018-01-03'
unionallselect'B11','50','香蕉','2018-01-05'
unionallselect'B11','-20','梨','2018-03-03'
unionallselect'B22','-50','葡萄','2018-03-08'
selecta.id,a.total,b.kind,b.theDatefrom(
selectid,sum(total)astotal
fromt
groupbyid
)asa
innerjoin(
selectROW_NUMBER()over(partitionbyidorderbytheDatedesc)asrid,*
fromt
)asb
ona.id=b.idandb.rid=1
/*
idtotalkindtheDate
-----------------------------------------
B1140梨2018-03-03
B22-50葡萄2018-03-08
*/

千问 · 2021-1-27 05:31:20

--测试数据
ifnotobject_id(N'Tempdb..#T')isnull
droptable#T
Go
Createtable#T([ID]nvarchar(23),[TOTAL]int,[KIND]nvarchar(22),[日期]Date)
Insert#T
selectN'B11',10,N'苹果','2018-01-03'unionall
selectN'B11',50,N'香蕉','2018-01-05'unionall
selectN'B11',-20,N'梨','2018-03-03'unionall
selectN'B22',-50,N'葡萄','2018-03-08'
Go
--测试数据结束
SELECTID,TOTAL,KIND,日期
FROM(SELECTID,
SUM(TOTAL)OVER(PARTITIONBYID)ASTOTAL,
KIND,
日期,
ROW_NUMBER()OVER(PARTITIONBYIDORDERBY日期DESC)ASrn
FROM#T
)t
WHERErn=1

千问 · 2021-1-27 05:31:20

分完组统计的时候，直接用max（日期）作为不就可以了

千问 · 2021-1-27 05:31:20

e.g.
WITHCTET(id,TOTAL,KIND,theDate)
AS(
select'B11',10,'苹果','2018-01-03'
unionallselect'B11',50,'香蕉','2018-01-05'
unionallselect'B11',-20,'梨','2018-03-03'
unionallselect'B22',-50,'葡萄','2018-03-08'
)
SELECTa.id
,a.TOTAL
,b.KIND
,b.theDate
FROM
(SELECTid,SUM(TOTAL)ASTOTALFROMCTETGROUPBYid)ASa
CROSSAPPLY
(SELECTTOP1
KIND
,theDate
FROMCTET
WHEREid=a.id
ORDERBYtheDateDESC)ASb;
/*
idTOTALKINDtheDate
B1140梨2018-03-03
B22-50葡萄2018-03-08
*/

千问 · 2021-1-27 05:31:20

;WITHCET1AS(
SELECTt.id,
SUM(t.TOTAL)ASTOTAL
FROMtASt
GROUPBY
t.id
),
CET2AS(
SELECTt.id,
t.TOTAL,
t.KIND,
t.theDate
FROMtASt
WHEREt.theDate=(
SELECTMAX(t2.theDate)
FROMtASt2
WHEREt2.id=t.id
)
)
SELECTCET1.id,
CET1.TOTAL,
CET2.KIND,
CET2.theDate
FROMCET1
JOINCET2
ONCET1.id=CET2.id