求助 sql查询语句

Question

declare @leibie table (名称 varchar(1),类别代码 varchar(4))
insert into @leibie
select 'a','1345' union all
select 'b','4526' union all
select 'c','7526' union all
select 'd','7562' union all
select 'e','7841'
declare @jigou table (名称 varchar(10),机构代码 varchar(6))
insert into @jigou
select 'jigou_a','158345' union all
select 'jigou_b','45d526' union all
select 'jigou_c','7s5d26' union all
select 'jigou_d','7sd562' union all
select 'jigou_e','7g84d1'declare @shanghu table (名称 varchar(10),机构代码 varchar(6),类别代码 varchar(4))
insert into @shanghu
select 'shanghu1','158345','1345' union all
select 'shanghu2','45d526','4526' union all
select 'shanghu3','158345','1345' union all
select 'shanghu4','7s5d26','4526' union all
select 'shanghu5','158345' ,'7526' union all
select 'shanghu6','7sd562' ,'7841' union all
select 'shanghu7','7s5d26' ,'7562' union all
select 'shanghu8','7g84d1' ,'7841' union all
select 'shanghu9','7sd562' ,'7562' union all
select 'shanghu10','7g84d1' ,'7562' union all
select 'shanghu10','7g84d1','4526'要下面这种结果：/*
名称      类别a      类别b       类别c        类别d        类别e      合计
----   ---------- ----------- ----------- ----------- ----------- ---------
jigou_a    2           0          1            0            0          3
jigou_b    0           1          0            0            0          1
jigou_c    0           1          0            1            1          2
jigou_d    0           0          0            1            1          2
jigou_e    0           1          0            1            1          3
合计       2           3          1            3            3          12
*/
说明：三个表：机构表、类别表、商户表
     根据类别查询商户的数量并根据机构分组

Answer 1

3表联查 行列转换 GROUP BY WITH ROLLUP

Answer 2

declare @leibie table (名称 varchar(1),类别代码 varchar(4))
insert into @leibie
select 'a','1345' union all
select 'b','4526' union all
select 'c','7526' union all
select 'd','7562' union all
select 'e','7841'declare @jigou table (名称 varchar(10),机构代码 varchar(6))
insert into @jigou
select 'jigou_a','158345' union all
select 'jigou_b','45d526' union all
select 'jigou_c','7s5d26' union all
select 'jigou_d','7sd562' union all
select 'jigou_e','7g84d1'declare @shanghu table (名称 varchar(10),机构代码 varchar(6),类别代码 varchar(4))
insert into @shanghu
select 'shanghu1','158345','1345' union all
select 'shanghu2','45d526','4526' union all
select 'shanghu3','158345','1345' union all
select 'shanghu4','7s5d26','4526' union all
select 'shanghu5','158345' ,'7526' union all
select 'shanghu6','7sd562' ,'7841' union all
select 'shanghu7','7s5d26' ,'7562' union all
select 'shanghu8','7g84d1' ,'7841' union all
select 'shanghu9','7sd562' ,'7562' union all
select 'shanghu10','7g84d1' ,'7562' union all
select 'shanghu10','7g84d1','4526'select c.名称,
类别a=sum(case when b.名称='a' then 1 else 0 end) ,
类别b=sum(case when b.名称='b' then 1 else 0 end) ,
类别c=sum(case when b.名称='c' then 1 else 0 end) ,
类别d=sum(case when b.名称='d' then 1 else 0 end) ,
类别e=sum(case when b.名称='e' then 1 else 0 end) ,
合计=count(1)
from @shanghu a 
left join @leibie b on a.类别代码=b.类别代码
left join @jigou c on a.机构代码=c.机构代码
group by c.名称 with rollup
/*
名称         类别a         类别b         类别c         类别d         类别e         合计
---------- ----------- ----------- ----------- ----------- ----------- -----------
jigou_a    2           0           1           0           0           3
jigou_b    0           1           0           0           0           1
jigou_c    0           1           0           1           0           2
jigou_d    0           0           0           1           1           2
jigou_e    0           1           0           1           1           3
NULL       2           3           1           3           2           11
*/

Answer 3

--我把合计两个字补充上
select 名称=ISNULL(c.名称,'合计'),
    类别a=sum(case when b.名称='a' then 1 else 0 end) ,
    类别b=sum(case when b.名称='b' then 1 else 0 end) ,
    类别c=sum(case when b.名称='c' then 1 else 0 end) ,
    类别d=sum(case when b.名称='d' then 1 else 0 end) ,
    类别e=sum(case when b.名称='e' then 1 else 0 end) ,
    合计=count(1)
from @shanghu a 
left join @leibie b on a.类别代码=b.类别代码
left join @jigou c on a.机构代码=c.机构代码
group by c.名称 with rollup/*
名称         类别a         类别b         类别c         类别d         类别e         合计
---------- ----------- ----------- ----------- ----------- ----------- -----------
jigou_a    2           0           1           0           0           3
jigou_b    0           1           0           0           0           1
jigou_c    0           1           0           1           0           2
jigou_d    0           0           0           1           1           2
jigou_e    0           1           0           1           1           3
合计         2           3           1           3           2           11
*/

Answer 4

create table  leibie(名称 varchar(1),类别代码 varchar(4))
insert into leibie
select 'a','1345' union all
select 'b','4526' union all
select 'c','7526' union all
select 'd','7562' union all
select 'e','7841'
create table  jigou(名称 varchar(10),机构代码 varchar(6))
insert into jigou
select 'jigou_a','158345' union all
select 'jigou_b','45d526' union all
select 'jigou_c','7s5d26' union all
select 'jigou_d','7sd562' union all
select 'jigou_e','7g84d1'create table  shanghu(名称 varchar(10),机构代码 varchar(6),类别代码 varchar(4))
insert into shanghu
select 'shanghu1','158345','1345' union all
select 'shanghu2','45d526','4526' union all
select 'shanghu3','158345','1345' union all
select 'shanghu4','7s5d26','4526' union all
select 'shanghu5','158345' ,'7526' union all
select 'shanghu6','7sd562' ,'7841' union all
select 'shanghu7','7s5d26' ,'7562' union all
select 'shanghu8','7g84d1' ,'7841' union all
select 'shanghu9','7sd562' ,'7562' union all
select 'shanghu10','7g84d1' ,'7562' union all
select 'shanghu10','7g84d1','4526'declare @sql varchar(8000)
select 
  @sql=isnull(@sql+',','')
  +'sum(case when a.名称='''+名称+''' then 1 else 0 end) as [类别'+名称+']'
from
(select distinct 名称 from leibie) tset @sql='select isnull(b.名称,''合计'') as [名称],'+@sql
  +',count(1) as [合计] from leibie a,jigou b,shanghu c 
    where a.类别代码=c.类别代码 and b.机构代码=c.机构代码
    group by b.名称 with rollup'
exec (@sql)/**
名称         类别a         类别b         类别c         类别d         类别e         合计
---------- ----------- ----------- ----------- ----------- ----------- -----------
jigou_a    2           0           1           0           0           3
jigou_b    0           1           0           0           0           1
jigou_c    0           1           0           1           0           2
jigou_d    0           0           0           1           1           2
jigou_e    0           1           0           1           1           3
合计         2           3           1           3           2           11(6 行受影响)
**/

Answer 5

类别是不确定的，还有可能会添加，所以不能这样写死了，when b.名称='a' 。  
类别e=sum(case when b.名称='e' then 1 else 0 end)

Answer 6

我的有点复杂declare @leibie table (名称 varchar(1),类别代码 varchar(4))
insert into @leibie
select 'a','1345' union all
select 'b','4526' union all
select 'c','7526' union all
select 'd','7562' union all
select 'e','7841'
declare @jigou table (名称 varchar(10),机构代码 varchar(6))
insert into @jigou
select 'jigou_a','158345' union all
select 'jigou_b','45d526' union all
select 'jigou_c','7s5d26' union all
select 'jigou_d','7sd562' union all
select 'jigou_e','7g84d1'declare @shanghu table (名称 varchar(10),机构代码 varchar(6),类别代码 varchar(4))
insert into @shanghu
select 'shanghu1','158345','1345' union all
select 'shanghu2','45d526','4526' union all
select 'shanghu3','158345','1345' union all
select 'shanghu4','7s5d26','4526' union all
select 'shanghu5','158345' ,'7526' union all
select 'shanghu6','7sd562' ,'7841' union all
select 'shanghu7','7s5d26' ,'7562' union all
select 'shanghu8','7g84d1' ,'7841' union all
select 'shanghu9','7sd562' ,'7562' union all
select 'shanghu10','7g84d1' ,'7562' union all
select 'shanghu10','7g84d1','4526'select case when a.名称 is not null then a.名称
when b.名称 is not null then b.名称
when c.名称 is not null then c.名称
when d.名称 is not null then d.名称
when e.名称 is not null then e.名称
end as 名称,
case when a.f_count is not null then a.f_count else 0 end as 类别a,
case when b.f_count is not null then b.f_count else 0 end as 类别b,
case when c.f_count is not null then c.f_count else 0 end as 类别c,
case when d.f_count is not null then d.f_count else 0 end as 类别d,
case when e.f_count is not null then e.f_count else 0 end as 类别e
from
(  
select a.名称,count(*) as f_count
  from @jigou as a
  join (select * from @leibie where 名称='a') as b on 1=1
  join @shanghu as c on a.机构代码=c.机构代码 and b.类别代码=c.类别代码
group by a.名称
) as a
full join
(
select a.名称,count(*) as f_count
  from @jigou as a
  join (select * from @leibie where 名称='b') as b on 1=1
  join @shanghu as c on a.机构代码=c.机构代码 and b.类别代码=c.类别代码
group by a.名称
) as b on a.名称=b.名称
full join
(
select a.名称,count(*) as f_count
  from @jigou as a
  join (select * from @leibie where 名称='c') as b on 1=1
  join @shanghu as c on a.机构代码=c.机构代码 and b.类别代码=c.类别代码
group by a.名称
) as c on a.名称=c.名称 or b.名称=c.名称
full join
(
select a.名称,count(*) as f_count
  from @jigou as a
  join (select * from @leibie where 名称='d') as b on 1=1
  join @shanghu as c on a.机构代码=c.机构代码 and b.类别代码=c.类别代码
group by a.名称
) as d on a.名称=d.名称 or b.名称=d.名称 or c.名称=d.名称
full join
(
select a.名称,count(*) as f_count
  from @jigou as a
  join (select * from @leibie where 名称='e') as b on 1=1
  join @shanghu as c on a.机构代码=c.机构代码 and b.类别代码=c.类别代码
group by a.名称
) as e on a.名称=e.名称 or b.名称=e.名称 or c.名称=e.名称 or d.名称=e.名称
order by case when a.名称 is not null then a.名称
when b.名称 is not null then b.名称
when c.名称 is not null then c.名称
when d.名称 is not null then d.名称
when e.名称 is not null then e.名称
end
名称 类别a 类别b 类别c 类别d 类别e
jigou_a 2 0 1 0 0
jigou_b 0 1 0 0 0
jigou_c 0 1 0 1 0
jigou_d 0 0 0 1 1
jigou_e 0 1 0 1 1