select b.* from table2 b left join table1 a on charindex(a.a,b.b)>0 order by case when a.a is not null then 1 else 2 end /** b -------------------- baab bab vv ff(4 行受影响) **/
select b from table2 order by (select count(1) from table1 where charindex(a,b)>0) desc /* b -------------------- baab bab vv ff */
create table table1(a varchar(20)); create table table2(b varchar(20));insert into table1 values('aa'); insert into table2 values('vv'); insert into table2 values('ff'); insert into table2 values('baab'); insert into table2 values('bab');select * from table2 order by CHARINDEX((select a from table1),b) desc/* b ------------------------ baab bab vv ff */
from table2 b left join table1 a on charindex(a.a,b.b)>0
order by
case when a.a is not null then 1 else 2 end
/**
b
--------------------
baab
bab
vv
ff(4 行受影响)
**/
select b from table2
order by (select count(1) from table1 where charindex(a,b)>0) desc
/*
b
--------------------
baab
bab
vv
ff
*/
create table table1(a varchar(20));
create table table2(b varchar(20));insert into table1 values('aa');
insert into table2 values('vv');
insert into table2 values('ff');
insert into table2 values('baab');
insert into table2 values('bab');select * from table2
order by CHARINDEX((select a from table1),b) desc/*
b
------------------------
baab
bab
vv
ff
*/