关于select多表联查的问题

[netang]

--建立业务A表
create table business_A
(
	A_id int identity(1,1) primary key,
	A_name varchar(10)
)
go
insert into business_A values ('A业务')
--建立业务B表
create table business_B
(
	B_id int identity(1,1) primary key,
	B_name varchar(10)
)
go
insert into business_B values ('B业务')
--建立订单表
create table business_bill
(
	businessName varchar(10),--业务表名
	businessID int,--业务表主键
)
go
insert into business_bill values ('business_A','1')
insert into business_bill values ('business_B','1')
--数据库建立完毕
select * from business_A
select * from business_B
select * from business_bill

想得到该结果，该如何进行呢？
businessName businessID TableName
business_A 1 A业务
business_B 1 B业务

[昵称被占用了]

怎么说呢，很多人希望有这样的设计，但是实际查询很难做到，反过来说明设计不可行

[中国风]

用動態或

--指定顯示列
select * from business_bill a inner join business_A b on a.ID=b.ID and a.businessName='business_A'

select * from business_bill a inner join business_B b on a.ID=b.ID and a.businessName='business_B'

[SQL77]

学习

[中国风]

--指定顯示列
select a.*,b.A_Name from business_bill a inner join business_A b on a.ID=b.ID and a.businessName='business_A'
union all
select a.*,b.B_Name from business_bill a inner join business_B b on a.ID=b.ID and a.businessName='business_B'

[bychgh]

DDDDD

[中国风]

declare @S nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select businessName='+quotename(businessName,'''')+',businessID='+rtrim(businessID)+','+right(businessName,1)+'_name from '+businessName+' where '+right(businessName,1)+'_id='+rtrim(businessID) from business_bill
exec(@s)

動態寫法列名和表名的取法可通過系統表取主健

[--小F--]

学习

[devilidea]

学习

[xiequan2]

看来只能union all了,要是看起来像联一个表的话，先弄个视图包装一下

[zhangjiang264]

学习了