FNumber FName FModel
02111990 AAA BBB
W02111990 AAA BBB
H02111990 AAA BBB02111991 AAA BBB
H02111991 AAA BBB02111992 AAA BBB
W02111992 AAA BBB现在想通过一条语句查询 FNumber 是否包含H 是则取带 H的这一条;如果不带H,则判断是否带W 是取带W这条;否则取 剩下不带前缀的
02111990 AAA BBB
W02111990 AAA BBB
H02111990 AAA BBB02111991 AAA BBB
H02111991 AAA BBB02111992 AAA BBB
W02111992 AAA BBB现在想通过一条语句查询 FNumber 是否包含H 是则取带 H的这一条;如果不带H,则判断是否带W 是取带W这条;否则取 剩下不带前缀的
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([FNumber] varchar(9),[FName] varchar(3),[FModel] varchar(3))
insert [tb]
select '02111990','AAA','BBB' union all
select 'W02111990','AAA','BBB' union all
select 'H02111990','AAA','BBB' union all
select '02111991','AAA','BBB' union all
select 'H02111991','AAA','BBB' union all
select '02111992','AAA','BBB' union all
select 'W02111992','AAA','BBB'
---查询---
select *
from tb t
where fnumber in(
select top 1 fnumber
from tb
where replace(replace(fnumber,'W',''),'H','')=replace(replace(t.fnumber,'W',''),'H','')
order by
case
when charindex('H',fnumber)>0 then 1
when charindex('W',fnumber)>0 then 2
else 3
end
)---结果---
FNumber FName FModel
--------- ----- ------
H02111990 AAA BBB
H02111991 AAA BBB
W02111992 AAA BBB(3 行受影响)
不只是不是你想要的结果,这是把FNumber里面含有'H'和'W'取出来