table1
id(主键),name
table2
id,typeid,count,table1id(外键)
table3
typeid,typename这三张表查询成一张表可能我说的还有些不清楚,请大家帮帮忙。
id(主键),name
table2
id,typeid,count,table1id(外键)
table3
typeid,typename这三张表查询成一张表可能我说的还有些不清楚,请大家帮帮忙。
where table1.id=table2.table1id and table2.typeid=table3.typeid
10 aa
20 bb
table2
1 1 100 10
2 2 200 20
table3
1 勺子
2 筷子
inner join table3 c on b.typeid=c.typeid
id(主键),name
table2
id,typeid,count,table1id(外键)
table3
typeid,typename
select tb1.id,tb1.name,tb2.id,tb2.typeid,tb2.count,tb3.typename from table1 tb1 left outer join table2 tb2 on tb1.id=tb2.table1id left outer join table3 tb3 on tb2.typeid=tb3.typeid
10 aa
11 bb
12 cc
table2
1 1 100 10
2 2 200 11
3 1 105 11
4 1 106 12
5 2 107 12
table3
1 勺子
2 筷子
结果
10 aa 勺子100 筷子null
11 bb 勺子105 筷子200
12 cc 勺子106 筷子107
declare @sql varchar(8000)
set @sql = 'select id,name '
select @sql = @sql + ',''+typename+'',sum(case typename when '''+typename+'''
then count else 0 end) as '''+typename
from (select distinct typename from table3) as a
select @sql = left(@sql,len(@sql)-1) + ' from (select a.id,a.name,b.count,c.typename from table1,table2,table3
where table1.id=table2.table1id and table2.typeid=table3.typeid ) a group by id,name'
exec(@sql)
go
没测试,应是样
insert into #table1
select 10 ,'aa' union all
select 11 ,'bb' union all
select 12 ,'cc'create table #table2(id int,typeid int,[count] int, table1id int)
insert into #table2
select 1 ,1 ,100 ,10 union allselect 2, 2 ,200, 11 union all
select 3, 1 ,105, 11 union allselect 4, 1 ,106, 12 union all
select 5, 2 ,107, 12
create table #table3(typeid int,typename varchar(20))
insert into #table3
select 1, '勺子'union all
select 2, '筷子'
declare @sql varchar(8000)
set @sql = 'select id,name '
select @sql = @sql + ','''+typename+''',sum(case typename when '''+typename+'''
then count else 0 end) as '''+typename+''''
from (select distinct typename from #table3) as a
select @sql = @sql + ' from (select a.id,a.name,b.[count],c.typename from #table1 a,#table2 b,#table3 c
where a.id=b.table1id and b.typeid=c.typeid ) a group by id,name'exec(@sql)
go
----------- -------------------- ---- ----------- ---- -----------
10 aa 筷子 0 勺子 100
11 bb 筷子 200 勺子 105
12 cc 筷子 107 勺子 106
----------- -------------------- ----------- ---- -----------
10 aa 0 100
11 bb 200 105
12 cc 107 106