select * from tb order by (case when m1 <> 0 then 1 else 0 end) + (case when m2 <> 0 then 1 else 0 end) + (case when m3 <> 0 then 1 else 0 end) + (case when m4 <> 0 then 1 else 0 end) + (case when m5 <> 0 then 1 else 0 end) desc
保险点select * from tab order by case when m1<>0 then 1 end+ case when m2<>0 then 1 end+ case when m3<>0 then 1 end+ case when m4<>0 then 1 end+ case when m5<>0 then 1 end
create table tb(dw varchar(10) , m1 int,m2 int,m3 int,m4 int,m5 int ) insert into tb values('a', 1 , 0 , 1 ,0 , 1) insert into tb values('b', 0 , 1 , 0 ,1 , 0) insert into tb values('c', 0 , 0 , 1 ,1 , 1) insert into tb values('d', 1 , 1 , 1 ,0 , 1) go select * from tb order by (case when m1 <> 0 then 1 else 0 end) + (case when m2 <> 0 then 1 else 0 end) + (case when m3 <> 0 then 1 else 0 end) + (case when m4 <> 0 then 1 else 0 end) + (case when m5 <> 0 then 1 else 0 end) desc drop table tb/* dw m1 m2 m3 m4 m5 ---------- ----------- ----------- ----------- ----------- ----------- d 1 1 1 0 1 c 0 0 1 1 1 a 1 0 1 0 1 b 0 1 0 1 0(4 行受影响) */
select * from tb order by (m1 + m2 + m3 + m4 + m5) desc
order by (case when m1 <> 0 then 1 else 0 end) +
(case when m2 <> 0 then 1 else 0 end) +
(case when m3 <> 0 then 1 else 0 end) +
(case when m4 <> 0 then 1 else 0 end) +
(case when m5 <> 0 then 1 else 0 end)
desc
order by case when m1<>0 then 1 end+
case when m2<>0 then 1 end+
case when m3<>0 then 1 end+
case when m4<>0 then 1 end+
case when m5<>0 then 1 end
insert into tb values('a', 1 , 0 , 1 ,0 , 1)
insert into tb values('b', 0 , 1 , 0 ,1 , 0)
insert into tb values('c', 0 , 0 , 1 ,1 , 1)
insert into tb values('d', 1 , 1 , 1 ,0 , 1)
go
select * from tb
order by (case when m1 <> 0 then 1 else 0 end) +
(case when m2 <> 0 then 1 else 0 end) +
(case when m3 <> 0 then 1 else 0 end) +
(case when m4 <> 0 then 1 else 0 end) +
(case when m5 <> 0 then 1 else 0 end)
desc drop table tb/*
dw m1 m2 m3 m4 m5
---------- ----------- ----------- ----------- ----------- -----------
d 1 1 1 0 1
c 0 0 1 1 1
a 1 0 1 0 1
b 0 1 0 1 0(4 行受影响)
*/
order by (m1 + m2 + m3 + m4 + m5) desc