select b.* from b join a on a.id=b.id order by Disabled
try. SELECT T.* FROM a T JOIN b ON T.id=b.aid ORDER BY b.Disabled
没说清楚b表里只要Disabled=0的其他不要用来排序
CREATE TABLE a ( id INT, time VARCHAR(20) ) INSERT INTO a SELECT 1 ,'XXXX' UNION ALL SELECT 2 ,'XXXX' UNION ALL SELECT 3 ,'XXXX' b表 CREATE TABLE b ( id INT, aid INT, Disabled INT ) INSERT INTO b SELECT 1, 2, 1 UNION ALL SELECT 2, 1, 0 UNION ALL SELECT 3, 1, 0SELECT T.* FROM a T LEFT JOIN b ON(T.id=b.aid AND b.Disabled=0) ORDER BY ISNULL(b.Disabled,1),T.id DESC /* id time ----------- -------------------- 1 XXXX 1 XXXX 3 XXXX 2 XXXX(4 行受影响) */
select level=0,* from a where exists(select 1 from b where a.id=b.id and b.disabled=0) union all select level=1,* from a where not exists(select 1 from b where a.id=b.id and b.disabled=0) order by level,id asc
CREATE TABLE a ( id INT, time VARCHAR(20) ) INSERT INTO a SELECT 1 ,'XXXX' UNION ALL SELECT 2 ,'XXXX' UNION ALL SELECT 3 ,'XXXX' b表 CREATE TABLE b ( id INT, aid INT, Disabled INT ) INSERT INTO b SELECT 1, 2, 1 UNION ALL SELECT 2, 1, 0 UNION ALL SELECT 3, 1, 0select a.* from a order by case when exists(select 1 from b where b.aid = a.id and b.Disabled = 0) then 1 when exists(select 1 from b where b.aid = a.id ) then 2 else 3 end drop table a , b/* id time ----------- -------------------- 1 XXXX 2 XXXX 3 XXXX(所影响的行数为 3 行) */
from b join a on a.id=b.id
order by Disabled
SELECT T.* FROM a T JOIN b ON T.id=b.aid ORDER BY b.Disabled
(
id INT,
time VARCHAR(20)
)
INSERT INTO a
SELECT
1 ,'XXXX' UNION ALL SELECT
2 ,'XXXX' UNION ALL SELECT
3 ,'XXXX'
b表
CREATE TABLE b
(
id INT,
aid INT,
Disabled INT
)
INSERT INTO b
SELECT
1, 2, 1 UNION ALL SELECT
2, 1, 0 UNION ALL SELECT
3, 1, 0SELECT T.* FROM a T LEFT JOIN b ON(T.id=b.aid AND b.Disabled=0) ORDER BY ISNULL(b.Disabled,1),T.id DESC
/*
id time
----------- --------------------
1 XXXX
1 XXXX
3 XXXX
2 XXXX(4 行受影响)
*/
union all
select level=1,* from a where not exists(select 1 from b where a.id=b.id and b.disabled=0)
order by level,id asc
(
id INT,
time VARCHAR(20)
)
INSERT INTO a
SELECT
1 ,'XXXX' UNION ALL SELECT
2 ,'XXXX' UNION ALL SELECT
3 ,'XXXX'
b表
CREATE TABLE b
(
id INT,
aid INT,
Disabled INT
)
INSERT INTO b
SELECT
1, 2, 1 UNION ALL SELECT
2, 1, 0 UNION ALL SELECT
3, 1, 0select a.* from a
order by
case when exists(select 1 from b where b.aid = a.id and b.Disabled = 0) then 1
when exists(select 1 from b where b.aid = a.id ) then 2
else 3
end
drop table a , b/*
id time
----------- --------------------
1 XXXX
2 XXXX
3 XXXX(所影响的行数为 3 行)
*/