如果按照另一个表排序？解决方法-MSSQL教程-爱易网页

如果按照另一个表排序？解决方法

日期：2014-05-18　浏览次数：20619 次

如果按照另一个表排序？

SQL code


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 a from table1
select b from table2

drop table table1
drop table table2

有两个表，table2如何按照table1排序呢, 也就是说table2的值包含table1的值拍在最前面, 只返回table2的数据 table2中 baab在最前面

------解决方案--------------------

SQL code

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 行受影响)
**/

------解决方案--------------------

SQL code
select b from table2 
order by (select count(1) from table1 where charindex(a,b)>0) desc
/*
b
--------------------
baab
bab
vv
ff
*/

------解决方案--------------------

SQL code
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
*/