select organzie ,
isnull(select sum(count) from 表A where type = '1' and
subtype ='1' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '1' and
subtype ='2' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '2' and
subtype ='1' and organize = 表B.organize,0 ),
isnull(select sum(count) from 表A where type = '2' and
subtype ='2' and organize = 表B.organize ,0)
from 表B
isnull(select sum(count) from 表A where type = '1' and
subtype ='1' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '1' and
subtype ='2' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '2' and
subtype ='1' and organize = 表B.organize,0 ),
isnull(select sum(count) from 表A where type = '2' and
subtype ='2' and organize = 表B.organize ,0)
from 表B
declare @b table (organize char(1))
insert @a values (1,1,'A',100)
insert @a values (1,1,'A',20)
insert @a values (1,2,'A',200)
insert @a values (2,1,'A',150)
insert @a values (2,2,'B',100)insert @b values ('A')
insert @b values ('B')
insert @b values ('c')select organize,
isnull(sum(case when type=1 and subtype=1 then [count] end),0) 'subtype1_1',
isnull(sum(case when type=1 and subtype=2 then [count] end),0) 'subtype1_2',
isnull(sum(case when type=2 and subtype=1 then [count] end),0) 'subtype2_1',
isnull(sum(case when type=2 and subtype=2 then [count] end),0) 'subtype2_2'
from
(select isnull(type,0) type,isnull(subtype,0) subtype,isnull(BB.organize,0) organize,isnull([count],0) count from @a AA right join @b BB on AA.organize=BB.organize) A
group by organize
declare @b table (organize char(1))
insert @a values (1,1,'A',100)
insert @a values (1,1,'A',20)
insert @a values (1,2,'A',200)
insert @a values (2,1,'A',150)
insert @a values (2,2,'B',100)insert @b values ('A')
insert @b values ('B')
insert @b values ('c')
select b.organize,c.type,c.subtype,isnull(d.[count],0) as totalcount
from
@b as b cross join
(
select distinct type,subtype from @a
) as c left join
(
select type,subtype,organize,sum([count]) as [count] from @a group by type,subtype,organize
) as d
on c.type=d.type
and c.subtype= d.subtype
and b.organize=d.organize
order by b.organize,c.type,c.subtype
而且type subtype 的个数是不可确定的。比如大分类有100个,小分类有50个如果用select organzie ,
isnull(select sum(count) from 表A where type = '1' and
subtype ='1' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '1' and
subtype ='2' and organize = 表B.organize ,0),
isnull(select sum(count) from 表A where type = '2' and
subtype ='1' and organize = 表B.organize,0 ),
isnull(select sum(count) from 表A where type = '2' and
subtype ='2' and organize = 表B.organize ,0)
from 表B
这样是在已知的情况下做的。
from
表B as b cross join
(
select distinct type,subtype from 表A
) as c left join
(
select type,subtype,organize,sum([count]) as [count] from 表A group by type,subtype,organize
) as d
on c.type=d.type
and c.subtype= d.subtype
and b.organize=d.organize
order by b.organize,c.type,c.subtype
select organize,
isnull(sum(case when type=1 and subtype=1 then [count] end),0) 'subtype1_1',
isnull(sum(case when type=1 and subtype=2 then [count] end),0) 'subtype1_2',
isnull(sum(case when type=2 and subtype=1 then [count] end),0) 'subtype2_1',
isnull(sum(case when type=2 and subtype=2 then [count] end),0) 'subtype2_2'
from
(select isnull(type,0) type,isnull(subtype,0) subtype,isnull(BB.organize,0) organize,isnull([count],0) count from 表A AA right join 表B BB on AA.organize=BB.organize) A
group by organize
organize subtype1_1(第一大類的第一小類) subtype1_2 total
A 120 200 320
B 0 0 0
C 0 0 0
怎么做啊?
前面的
select b.organize,c.type,c.subtype,isnull(d.[count],0) as totalcount
from
表B as b cross join
(
select distinct type,subtype from 表A
) as c left join
(
select type,subtype,organize,sum([count]) as [count] from 表A group by type,subtype,organize
) as d
on c.type=d.type
and c.subtype= d.subtype
and b.organize=d.organize
order by b.organize,c.type,c.subtype
思路不错,可惜统计每个组织总分好象有点问题啊。对不起,我比较笨
你要试试大家给你写的代码呀!!!!!select organize,
isnull(sum(case when type=1 and subtype=1 then [count] end),0) 'subtype1_1',
isnull(sum(case when type=1 and subtype=2 then [count] end),0) 'subtype1_2',
isnull(sum(case when type=2 and subtype=1 then [count] end),0) 'subtype2_1',
isnull(sum(case when type=2 and subtype=2 then [count] end),0) 'subtype2_2'
from
(select isnull(type,0) type,isnull(subtype,0) subtype,isnull(BB.organize,0) organize,isnull([count],0) count from @a AA right join @b BB on AA.organize=BB.organize) A
group by organize
isnull(sum(case when type=1 and subtype=1 then [count] end),0) 'subtype1_1',
isnull(sum(case when type=1 and subtype=2 then [count] end),0) 'subtype1_2',
isnull(sum(case when type=2 and subtype=1 then [count] end),0) 'subtype2_1',
isnull(sum(case when type=2 and subtype=2 then [count] end),0) 'subtype2_2'
from
(select isnull(type,0) type,isnull(subtype,0) subtype,isnull(BB.organize,0) organize,isnull([count],0) count from @a AA right join @b BB on AA.organize=BB.organize) A
group by organize) AA
declare @a table (type int,subtype int,organize char(1),[count] int)
declare @b table (organize char(1))
insert @a values (1,1,'A',100)
insert @a values (1,1,'A',20)
insert @a values (1,2,'A',200)
insert @a values (2,1,'A',150)
insert @a values (2,2,'B',100)insert @b values ('A')
insert @b values ('B')
insert @b values ('c')select b.organize,c.type,c.subtype,isnull(d.[count],0) as totalcount,d.allcount
from
@b as b cross join
(
select distinct type,subtype from @a
) as c left join(select type,subtype,aa.organize,[count],allcount from
(select type,subtype,organize,sum([count]) as [count] from @a group by type,subtype,organize) aa
left join (select organize,sum([count]) as allcount from @a group by organize ) bb on aa.organize=bb.organize
) as d
on c.type=d.type
and c.subtype= d.subtype
and b.organize=d.organize
order by b.organize,c.type,c.subtype输出结果是
A 1 1 120 470
A 1 2 200 470
A 2 1 150 470
A 2 2 0 null
:
:
:
我要的输出结果是
A 1 1 120 470
A 1 2 200 470
A 2 1 150 470
A 2 2 0 470我要做的就是记录根据总分排序,470最多就排在第一,但是同一组织的四个分类又要在一起。怎么做啊?请教!
declare @b table (organize char(1))
insert @a values (1,1,'A',100)
insert @a values (1,1,'A',20)
insert @a values (1,2,'A',200)
insert @a values (2,1,'A',150)
insert @a values (2,2,'B',100)insert @b values ('A')
insert @b values ('B')
insert @b values ('c')
select temp3.organize,temp3.type,temp3.subtype,isnull(temp4.[count],0) [count],temp3.allcount from (
select organize,type,subtype,allcount from (select distinct type,subtype from @a ) temp1,
(select bb.organize,isnull(sum(aa.[count]),0) as allcount from @b bb left join @a aa on aa.organize=bb.organize group by bb.organize )temp2) temp3 left join
(select organize,type,subtype,sum([count]) [count] from @a group by organize,type,subtype) temp4 on temp3.organize=temp4.organize and temp3.type=temp4.type and temp3.subtype=temp4.subtype