如何分类汇总

mysql数据库多级分类汇总_sql多级分类汇总实现介绍

t1

idparentid

ma

na

em

fm

xf

yf

zb

t2

rowidamount

1a13.00

2b20.00

3e20.00

4f20.00

5x20.00

6y20.00

7z20.00

8e12.00

9x11.00

10f13.00

如何得出如下结果：

rowidamount

7x20.00

11x11.00

x⼩计31.00

8y20.00

y⼩计20.00

6f20.00

12f13.00

f⼩计84.00

5e20.00

10e12.00

e⼩计32.00

3m14.00

m⼩计130.00

4n13.00

n⼩计13.00

1a13.00

a⼩计156.00

9z20.00

z⼩计20.00

2b20.00

b⼩计40.00

总计196.00

实现程序

--⽰例数据

CREATETABLEt1(

idchar(1),

parentidchar(1)

);

INSERTt1

SELECT'm','a'UNIONALL

SELECT'n','a'UNIONALL

SELECT'e','m'UNIONALL

SELECT'f','m'UNIONALL

SELECT'x','f'UNIONALL

SELECT'y','f'UNIONALL

SELECT'z','b';

CREATETABLEt2(

rowint,

idchar(1),

amountdecimal(10,2)

);

INSERTt2

SELECT'1','a','13.00'UNIONALL

SELECT'2','b','20.00'UNIONALL

SELECT'3','e','20.00'UNIONALL

SELECT'4','f','20.00'UNIONALL

SELECT'5','x','20.00'UNIONALL

SELECT'6','y','20.00'UNIONALL

SELECT'7','z','20.00'UNIONALL

SELECT'8','e','12.00'UNIONALL

SELECT'9','x','11.00'UNIONALL

SELECT'10','f','13.00';

GO

--统计

--逐级汇总

declare@lint

t@l=1

lect

A.[id],

[pid]=id,

[sumnum]=SUM(),

level=ca

whenexists(lect*fromt1whereparentid=a.[id])

then@l-1el@lend

into[#]

fromt1A

LEFTJOINt2B

=

,id;

if@@row/get=_blank>count>0

createindexIDX_#_id_pidon[#]([id],[pid])

el

t@l=999

while@@rowcount>0or@l=1

begin

t@l=@l+1

updateatlevel=@l,[sumnum]=isnull(a.[sumnum],0)+isnull(b.[sumnum],0)

from[#]a,(

,[sumnum]=sum(aa.[sumnum])

from[#]aa,(

lectdistinct[pid]from[#]

wherelevel=@l-1

)bbwhereaa.[pid]=bb.[pid]

ANDNOTEXISTS(

SELECT*FROM[#]WHERE[PID]=aa.[PID]AND[Level]=0)

GROUPBYaa.[PID]

havingsum(=0then1el0end)=0

)bwherea.[id]=b.[pid]

end

--最终结果

SELECT

row=CASE

WHENGROUPING()=0THENRTRIM()

ELSEN''

END,

id=CASE

WHENGROUPING()=

WHENGROUPING()=+'⼩计'

ELSEN'总计'

END,

amount=CASE

WHENGROUPING()=0THENSUM()

WHENGROUPING()=0THENISNULL((SELECTSUM()FROM#=),SUM())

ELSESUM()

END

FROMt2A

,HROLLUP;

droptable[#]

GO

DROPTABLEt1,t2;

/*--结果

rowidamount

--------------------------------------------------------

1a13.00

a⼩计13.00

2b20.00

b⼩计20.00

3e20.00

8e12.00

e⼩计32.00

4f20.00

10f13.00

f⼩计84.00

5x20.00

9x11.00

x⼩计31.00

6y20.00

y⼩计20.00

7z20.00

z⼩计20.00

总计169.00

(18⾏受影响)

--*/