数据记录分表后的查询的问题

[benlao]

主表
id x table_id
1 x1 扩展01
2 x2 扩展01
3 x3 扩展02
4 x4 扩展03
5 x5 扩展03
6 x6 扩展03
...

扩展01
id y
1 y1
2 y2
...

扩展02
id y
3 y3
...

扩展03
id y
4 y4
5 y5
6 y6
...

实现结果：
id x y
1 x1 y1
2 x2 y2
3 x3 y3
4 x4 y4
5 x5 y5
6 x6 y6
...

[华夏小卒]

select a.id,a.x,y=isnull(isnull(b.y,c.y),d.y)
from 主表 a
left join 扩展01 b on a.id=b.id
left join 扩展02 c on a.id=c.id
left join 扩展03 d on a.id=d.id

??

[水族杰纶]

left join

[--小F--]

select 
  a.id,a.x,t.y
from
(select * from 扩展01
union all
select * from 扩展02
union all
select * from 扩展03)t
where
  a.id=t.id

[benlao]

我用一字段来记录 扩展表表名，就是说扩展表不是只有三个。

[benlao]

主表的table_id，没用吗，没有人去利用它

[bancxc]

呵呵 献丑

declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like '扩展%'


select @sql = 'select 
  a.id,a.x,t.y
from a,' + @sql + ' t where
  a.id=t.id' 
exec(@sql)

[bancxc]

改了一下啊

declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like '扩展%'

select @sql = '(' + @sql + ')'

select @sql = 'select a.id,a.x,t.y
from a inner join ' + @sql + ' t on  a.id=t.id' 
print (@sql)

exec(@sql)