--查询
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by (case flag
when 'I' then 1
when 'R' then 2
else 3
end
)
)
or B.name is null--删除测试环境
drop table tableA,tableB--结果
/*
code name
---------- ----------
A 李四
B 找六
C 謝軍
D '
E '(5 row(s) affected)
*/
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by (case flag
when 'I' then 1
when 'R' then 2
else 3
end
)
)
or B.name is null--删除测试环境
drop table tableA,tableB--结果
/*
code name
---------- ----------
A 李四
B 找六
C 謝軍
D '
E '(5 row(s) affected)
*/
from tablea a left join tableb b on a.code=b.code
group by a.code
/*
结果
code name
------------------
A 李四
B 找六
C 謝軍
D
E */
select a.code,isnull((select top 1 name from tableb where code=a.code order by (case flag when 'I' then 0 when 'R' then 1 else 2 end)),'''''') as name
from tablea a left join tableb b on a.code=b.code
group by a.code
/*
结果
code name
------------------
A 李四
B 找六
C 謝軍
D ''
E ''
*/
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.code=(select top 1 code from tableB where code=B.code order by (case flag
when 'I' then 1 when 'R' then 2 else 3 end) )
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO') --还有其他的就自己加
)
or B.name is null
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO') --还有其他的就自己加
)
or B.name is null
left join tableB B on A.code=B.code
where B.name=(select top 1 name from tableB where code=B.code
order by (case flag when 'I' then 1 when 'R' then 2 else 3 end))
or B.name is null
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO') --还有其他的就自己加
)
or B.name is null
create table tableA(id int ,code varchar(10));
create table tableB(id int ,code varchar(10),name varchar(10),flag char(1));
insert into tableA values(1,'A');
insert into tableA values(2,'B');
insert into tableA values(3,'C');
insert into tableA values(4,'D');
insert into tableA values(5,'E');insert into tableB values(90,'A','力钱','O');
insert into tableB values(91,'A','王丹','O');
insert into tableB values(99,'A','張三','O');
insert into tableB values(16,'A','李四','I');
insert into tableB values(13,'A','李四','R');
insert into tableB values(66,'B','找六','I');
insert into tableB values(567,'C','謝軍','I');select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO') --还有其他的就自己加
)
or B.name is null结果:A 李四
A 李四
B 找六
C 謝軍
D ''
E ''//也就是李四重复了.怎么去掉这个重复的?
select A.code ,isnull(B.name,'''') as 'name' from tableA A left join tableB B on A.code=B.code where B.name=(select top 1 name from tableB where code=B.code order by (case flag when 'I' then 1 when 'R' then 2 else 3 end )) or B.name is null
通過:
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO'))//返回字符串中指定表达式的起始位置。
or B.name is null
select DISTINCT T.* from (
select A.code
,isnull(B.name,'''') as 'name'
from tableA A
left join tableB B on A.code=B.code
where B.name=(select top 1 name
from tableB
where code=B.code
order by charindex(flag,'IRO') --还有其他的就自己加
)
or B.name is null
) T