表Aid proId Name
1 1 a表B
id proId Name
1 1 b表C
id proId Name
1 1 c表D
id proId Name
1 2 d表project
id proName addDate AddUser
1 pro1 xxxx 123查出结果
id proName addDate AddUser hasA hasB hasC hasD
1 pro1 xx 123 1 1 1 0
1 1 a表B
id proId Name
1 1 b表C
id proId Name
1 1 c表D
id proId Name
1 2 d表project
id proName addDate AddUser
1 pro1 xxxx 123查出结果
id proName addDate AddUser hasA hasB hasC hasD
1 pro1 xx 123 1 1 1 0
select
a.*,isnull(b.proid,0) as hasA,
isnull(c.proid,0) as hasB,
isnull(d.proid,0) as hasC,
isnull(e.proid,0) as hasD
from project a
inner join 表A b on a.proid=b.proid
inner join 表B c on a.proid=c.proid
inner join 表C d on a.proid=d.proid
inner join 表D e on a.proid=e.proid
--> 测试数据:[表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A]([id] int,[proId] int,[Name] varchar(1))
insert [表A]
select 1,1,'a'
--> 测试数据:[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([id] int,[proId] int,[Name] varchar(1))
insert [表B]
select 1,1,'b'
--> 测试数据:[表C]
if object_id('[表C]') is not null drop table [表C]
create table [表C]([id] int,[proId] int,[Name] varchar(1))
insert [表C]
select 1,1,'c'
--> 测试数据:[表D]
if object_id('[表D]') is not null drop table [表D]
create table [表D]([id] int,[proId] int,[Name] varchar(1))
insert [表D]
select 1,2,'d'
--> 测试数据:[project]
if object_id('[project]') is not null drop table [project]
create table [project](
[id] int,
[proName] varchar(4),
[addDate] varchar(4),
[AddUser] int
)
insert [project]
select 1,'pro1','xxxx',123select
a.*,isnull(b.proid,0) as hasA,
isnull(c.proid,0) as hasB,
isnull(d.proid,0) as hasC,
isnull(e.proid,0) as hasD
from project a
left join 表A b on a.id=b.[proId]
left join 表B c on a.id=c.[proId]
left join 表C d on a.id=d.[proId]
left join 表D e on a.id=e.[proId]/*
id proName addDate AddUser hasA hasB hasC hasD
1 pro1 xxxx 123 1 1 1 0
*/
这样对吗??