关于树型结构的SQL语句怎么写,请各位大侠帮忙,急!!!!!!!!(高分)在线等待!!!

有表A
其中记录是这样的
小类 数量
10000 50
10001 50
10002 25
10003 25
这些小类都从属于大类100里面就是其树型结构 ,要求用SQL语句使其输出下列结果:
大类 小类 数量 占大类比
100 10000 50 33%
100 10001 50 33%
100 10002 25 17%
100 10003 25 17%
各位大侠帮帮忙,在这先谢了!!!!!!急呀!!!!

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

select 大类,小类,a.数量,cast(a.数量/b.数量*100 as varchar)+'%' as 占大类比
from A a left outer join
(select left(小类,3) as 大类,sum(数量) as 数量 from A
group by left(小类,3)) b on a.left(小类,3) = b.大类;

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

select left(小类,3) as 大类,小类,数量,数量/(select sum(数量) from a a2 where left(a2.小类,3)=left(a1.小类,3)) as 占大类比
from a a1

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

用扬的方法:

select left(小类,3) as 大类,小类,sum(数量),sum(数量)/(select sum(数量) from a a2 where left(a2.小类,3)=left(a1.小类,3)) as 占大类比
from a a1
group by left(小类,3),小类

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus