有表数据:
ID F1 Qty F3
1 A1 1 001
2 A1 1 001
3 A1 2 002
4 A1 3 003
5 B1 1 004
6 B1 1 005
7 B1 1 005
8 B1 2 005
9 C1 1 006要按:F1,Qty进行排序,然后再把具有相同的F3排在最后面,要得到以下结果:
ID F1 Qty F3
3 A1 2 002
4 A1 3 003
5 B1 1 004
9 C1 1 006
1 A1 1 001
2 A1 1 001
6 B1 1 005
7 B1 1 005
8 B1 2 005
ID F1 Qty F3
1 A1 1 001
2 A1 1 001
3 A1 2 002
4 A1 3 003
5 B1 1 004
6 B1 1 005
7 B1 1 005
8 B1 2 005
9 C1 1 006要按:F1,Qty进行排序,然后再把具有相同的F3排在最后面,要得到以下结果:
ID F1 Qty F3
3 A1 2 002
4 A1 3 003
5 B1 1 004
9 C1 1 006
1 A1 1 001
2 A1 1 001
6 B1 1 005
7 B1 1 005
8 B1 2 005
select *
from tb t
order by (case when exists (select 1 from tb where f1=t.f1 and qty=t.qty and id<>t.id) then 1 else 0 end),f1,qty,f3
declare @T table (ID int,F1 varchar(2),Qty int,F3 varchar(3))
insert into @T
select 1,'A1',1,'001' union all
select 2,'A1',1,'001' union all
select 3,'A1',2,'002' union all
select 4,'A1',3,'003' union all
select 5,'B1',1,'004' union all
select 6,'B1',1,'005' union all
select 7,'B1',1,'005' union all
select 8,'B1',2,'005' union all
select 9,'C1',1,'006'select * from @T t order by (select count(1) from @T where F3=t.F3)
/*
ID F1 Qty F3
----------- ---- ----------- ----
3 A1 2 002
4 A1 3 003
5 B1 1 004
9 C1 1 006
1 A1 1 001
2 A1 1 001
6 B1 1 005
7 B1 1 005
8 B1 2 005
*/
INSERT INTO @T
SELECT '1','A1','1','001' UNION ALL
SELECT '2','A1','1','001' UNION ALL
SELECT '3','A1','2','002' UNION ALL
SELECT '4','A1','3','003' UNION ALL
SELECT '5','B1','1','004' UNION ALL
SELECT '6','B1','1','005' UNION ALL
SELECT '7','B1','1','005' UNION ALL
SELECT '8','B1','2','005' UNION ALL
SELECT '9','C1','1','006'SELECT *
FROM (
SELECT (SELECT COUNT(1) FROM @T WHERE F3=T1.F3 GROUP BY F3) AS ORD, *
FROM @T T1
) A
ORDER BY ORD,F1,QTY
insert into tb
select 1,'A1',1,'001' union all
select 2,'A1',1,'001' union all
select 3,'A1',2,'002' union all
select 4,'A1',3,'003' union all
select 5,'B1',1,'004' union all
select 6,'B1',1,'005' union all
select 7,'B1',1,'005' union all
select 8,'B1',2,'005' union all
select 9,'C1',1,'006'select m.* from tb m , (
select f3 , count(1) cnt from tb group by f3) n
where m.f3 = n.f3
order by n.cnt , m.f1 , m.qtydrop table tb/*
ID F1 Qty F3
----------- ---- ----------- ----
3 A1 2 002
4 A1 3 003
5 B1 1 004
9 C1 1 006
1 A1 1 001
2 A1 1 001
6 B1 1 005
7 B1 1 005
8 B1 2 005(所影响的行数为 9 行)
*/