日期:2014-05-18  浏览次数:20523 次

一条联合的sql语句
表A

id 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

------解决方案--------------------
SQL code

--> 测试数据:[表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',123

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
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
*/


这样对吗??