这样的sql 怎样写

[renren6250]

表内容如下:
proj_id wbs_id type name
1 a 001 aa
1 b 001 bb
1 b 003 cc
1 c 001 cc
1 d 001 dd
1 d 003 ee
我想查询到如下内容
proj_id wbs_id type name name1
1 a 001 aa aa
1 b 003 bb cc
1 c 001 cc cc
1 d 003 ee dd
也就是说 如果 type是003，相同的proj_id ,wbs_id 而且type是001
这条记录就不显示,但他的 name 字段的值,显示在name1中,
这样的sql如何写啊
谢谢大虾了

[renren6250]

不好意思,上面的查询记录错了
表内容如下:
proj_id wbs_id type name
1 a 001 aa
1 b 001 bb
1 b 003 cc
1 c 001 cc
1 d 001 dd
1 d 003 ee
我想查询到如下内容
proj_id wbs_id type name name1
1 a 001 aa aa
1 b 003 cc bb
1 c 001 cc cc
1 d 003 ee dd
也就是说 如果 type是003，相同的proj_id ,wbs_id 而且type是001
这条记录就不显示,但他的 name 字段的值,显示在name1中,
这样的sql如何写啊
谢谢大虾了

[yujohny]

create table 表(proj_id int,wbs_id nvarchar(1),
type nvarchar(3),name nvarchar(2))

insert into 表 values(1,'a','001','aa')
insert into 表 values(1,'b','001','bb')
insert into 表 values(1,'b','003','cc')
insert into 表 values(1,'c','001','cc')
insert into 表 values(1,'d','001','dd')
insert into 表 values(1,'d','003','ee')

select a.proj_id,a.wbs_id, '001',a.[name],a.[name] name1
from 表 a left join 表 b on a.proj_id = b.proj_id
and a.wbs_id = b.wbs_id and b.type='003'
where a.type ='001' and b.proj_id is null
union
select a.proj_id,a.wbs_id,'003',max(a.[name]),
(select [name] from 表 b where b.proj_id=a.proj_id
and b.wbs_id=a.wbs_id and b.type='001') as name1
from 表 a
where a.type='003'
group by a.proj_id,a.wbs_id

drop table 表

[friendliu]

select distinct proj_id,wbs_id,type,name,name1
from aa

[happydreamer]

select *, case when type='001' then name else (select name from table where proj_id=a.proj_id and wbs_id=a.wbs_id and type='001') end
from table a
where type in (select top 1 type from table where proj_id=a.proj_id and wbs_id=a.wbs_id order by type desc)

[zjcxc]

select a.*
,name1=case type when '003' then (select name from 表 where type<>'003' and proj_id=a.proj_id and wbs_id=a.wbs_id)
else name end
from 表 a
where not exists(select 1 from 表 where a.type<>'003' and type='003' and proj_id=a.proj_id and wbs_id=a.wbs_id)

[zjcxc]

数据测试:

--数据环境
declare @tb table(proj_id int,wbs_id nvarchar(1),type nvarchar(3),name nvarchar(2))
insert into @tb
select 1,'a','001','aa'
union all select 1,'b','001','bb'
union all select 1,'b','003','cc'
union all select 1,'c','001','cc'
union all select 1,'d','001','dd'
union all select 1,'d','003','ee'

--查询结果
select a.*
,name1=case type when '003' then (select name from @tb where type<>'003' and proj_id=a.proj_id and wbs_id=a.wbs_id)
else name end
from @tb a
where not exists(select 1 from @tb where a.type<>'003' and type='003' and proj_id=a.proj_id and wbs_id=a.wbs_id)

/*--我的电脑上的测试结果:
proj_id wbs_id type name name1
----------- ------ ---- ---- -----
1 a 001 aa aa
1 b 003 cc bb
1 c 001 cc cc
1 d 003 ee dd

（所影响的行数为 4 行）
--*/

[txlicenhe]

select a.*
,name1=case type when '003' then (select name from 表 where type<>'003' and proj_id=a.proj_id and wbs_id=a.wbs_id)
else name end
from 表 a
where not exists(select 1 from 表 where a.type<>'003' and type='003' and proj_id=a.proj_id and wbs_id=a.wbs_id)

[txlicenhe]

select a.*
,name1=case type when '003' then (select name from 表 where type<>'003' and proj_id=a.proj_id and wbs_id=a.wbs_id)
else name end
from 表 a
where not exists(select 1 from 表 where a.type<>'003' and type='003' and proj_id=a.proj_id and wbs_id=a.wbs_id)

[txlicenhe]

select a.*
,name1=case type when '003' then (select name from 表 where type<>'003' and proj_id=a.proj_id and wbs_id=a.wbs_id)
else name end
from 表 a
where not exists(select 1 from 表 where a.type<>'003' and type='003' and proj_id=a.proj_id and wbs_id=a.wbs_id)