＋设为首页 ＋添加到收藏夹
表内容如下:
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如何写啊
谢谢大虾了
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 表
select distinct proj_id,wbs_id,type,name,name1
from aa
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)
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)
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)
