declare @s varchar(8000)
set @s='select b.c_name as s_name '
select @s=@s+',sum(case c.d_name when '''+d_name+''' then 1 else 0 end) as ['+d_name+']'
from (select distinct d_name from [tbl_3]) t
set @s=@s+',count(1) as [ji]
from [tbl_4] d
inner join [tbl_3] c on c.d_id=d.d_id
inner join [tbl_1] a on a.r_id=d.r_id
left join [tbl_2] b on a.c_id=b.c_id
group by b.c_name 'exec(@s)
set @s='select b.c_name as s_name '
select @s=@s+',sum(case c.d_name when '''+d_name+''' then 1 else 0 end) as ['+d_name+']'
from (select distinct d_name from [tbl_3]) t
set @s=@s+',count(1) as [ji]
from [tbl_4] d
inner join [tbl_3] c on c.d_id=d.d_id
inner join [tbl_1] a on a.r_id=d.r_id
left join [tbl_2] b on a.c_id=b.c_id
group by b.c_name 'exec(@s)
解决方案 »
- 三个表的数据 我想结合显示!怎么做!??如下面……
- 一次错误操作,把EXCEL 中部分数据改掉了,请问有什么办法恢复啊?
- 菜鸟小声问一下?
- 用一个用户在sqlserver建立一个表,想控制其他用户访问的权限:具体是不让别人看见表中的一列(只显示成为*),其他列可以看到。
- 求一分页SQL语句(在线等)
- 关于在一表中查询一组数值与另一组数值作对比的问题(详情请进)
- 求一简单的查询sql语句。
- 一个表A其中两个字段是nameid1,nameid2,都对应表B的id字段。
- 数据库REPLICATION 问题请教
- 拼接SQL ???
- help + sos + 119+110 帮俺看看一个雷人的需求。。。
- Tree结构数据表,如何返回所有指定好的所有项,以及其相关的所有父项
insert into tbl_1 select '001',1
insert into tbl_1 select '002',2
insert into tbl_1 select '003',1
insert into tbl_1 select '004',2
insert into tbl_1 select '005',1 create table tbl_2(c_id int,c_name varchar(10))
insert into tbl_2 select 1,'a'
insert into tbl_2 select 2,'b'
insert into tbl_2 select 3,'c'
insert into tbl_2 select 4,'d' create table tbl_3(d_id varchar(10),d_name varchar(10))
insert into tbl_3 select '01','A'
insert into tbl_3 select '02','B'
insert into tbl_3 select '03','C' create table tbl_4(r_id varchar(10),d_id varchar(10))
insert into tbl_4 select '001','02'
insert into tbl_4 select '002','01'
insert into tbl_4 select '003','02'
insert into tbl_4 select '004','01'
insert into tbl_4 select '005','02' declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+d_name+']=sum(case c.d_id when '''+d_id+''' then 1 else 0 end)'
from tbl_3set @sql='select b.c_name'+@sql+',count(*) as ji from tbl_1 a,tbl_2 b,tbl_4 c where a.c_id=b.c_id and a.r_id=c.r_id group by b.c_name'
exec(@sql)/*
c_name A B C ji
---------- ----------- ----------- ----------- -----------
a 0 3 0 3
b 2 0 0 2
*/drop table tbl_1,tbl_2,tbl_3,tbl_4