这样的交叉表怎样用SQL来做?(200分)

表ls 如下:
A B C D
A1 B1 C1 1
A1 B1 C1 2
A1 B1 C2 3
A1 B1 C2 4
A1 B2 C3 5
A1 B2 C3 6
A1 B2 C4 7
A1 B2 C4 8
A2 B3 C5 9
A2 B3 C5 10
A2 B3 C5 11
A2 B3 C5 12
A2 B4 C6 13
A2 B4 C6 14
A2 B4 C6 15
A2 B4 C6 16
用SQL语句做如下的交叉统计表:
A D
合计 136
A1 36
B1 10
C1 3
C2 7
B2 26
C3 11
C4 15
A2 100
B3 42
C5 19
C6 23
B4 58
C7 27
C8 31
期盼中......

分别select count(*) from ls order by a
select count(*) from ls order by b
select count(*) from ls order by c
然后合并

除了排序……
select '合计' as A, sum(D) as D from ls
union all
select A as A, sum(D) as D from ls group by A
union all
select B as A, sum(D) as D from ls group by B
union all
select C as A, sum(D) as D from ls group by C

select A=A, D=sum(D) from ls group by A
union all
select A=B, D=sum(D) from ls group by B
union all
select A=C, D=sum(D) from ls group by C

这是一个分级汇总的问题,而且次序不能排乱了.
就象我们统计一个地区的面积,行政级别有地区、县、乡、村。
村汇总到乡，乡汇总到县，县汇总到地区，这样逐级汇总。

if exists(select * from sysobjects where xtype='U' and name='tt')
drop table tt
go
create table tt(A varchar(50),B varchar(50),C varchar(50),D int)
BULK INSERT test2..tt FROM 'E:/1.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '/n'
)
go
if exists (select * from sysobjects where xtype='V' and name='vw_tt')
drop view vw_tt
go
if exists (select * from sysobjects where xtype='V' and name='vw_tt')
drop view vw_tt
go
create view vw_tt
as
select A,B,C,sum(D) as dd from tt group by A,B,C
union
select A,B,'',sum(D)as dd from tt group by A,B
union
select A,'','',sum(D) as dd from tt group by A
union
select ' 合计','','',sum(D) as dd from tt
go
select 列名=case
when A=' 合计' then
'合计'
when B='' and C='' then
A
when C='' then
B
else
C end ,dd from vw_tt order by A,B,C
这个可以得到你的结果，也是按照你的排序来的，记得合计前面有个空格，这样才能排到第一位
给分吧，呵呵！