22,294
社区成员
发帖
与我相关
我的任务
分享求助 sql查询语句
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
*/
说明：三个表：机构表、类别表、商户表
根据类别查询商户的数量并根据机构分组
...全文
请发表友善的回复…
发表回复
EverySoft 2012-01-14
- 打赏
- 举报
我的有点复杂
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
hu_service 2012-01-13
- 打赏
- 举报
[Quote=引用 4 楼 maco_wang 的回复:]
SQL code
--我把合计两个字补充上
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 ……
[/Quote]
类别是不确定的，还有可能会添加，所以不能这样写死了，when b.名称='a' 。
类别e=sum(case when b.名称='e' then 1 else 0 end)
SQL code
--我把合计两个字补充上
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 ……
[/Quote]
类别是不确定的，还有可能会添加，所以不能这样写死了，when b.名称='a' 。
类别e=sum(case when b.名称='e' then 1 else 0 end)
yanyuchonglou 2012-01-13
- 打赏
- 举报
看着有些面熟啊，近来好象有一批这样的问题。
wuqing9018 2012-01-13
- 打赏
- 举报
学习了
百年树人 2012-01-13
- 打赏
- 举报
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) t
set @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 行受影响)
**/
叶子 2012-01-13
- 打赏
- 举报
--我把合计两个字补充上
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
*/叶子 2012-01-13
- 打赏
- 举报
右下角的那个12我怎么算都是11。
叶子 2012-01-13
- 打赏
- 举报
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
*/
--小F-- 2012-01-13
- 打赏
- 举报
3表联查 行列转换 GROUP BY WITH ROLLUP
