22,210
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-07-23 18:30:02
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[f1] [nvarchar](10),[f2] [int],[f3] [int])
INSERT INTO [tb]
SELECT '1','a','1','5' UNION ALL
SELECT '2','b','5','8' UNION ALL
SELECT '3','b','3','12' UNION ALL
SELECT '4','c','3','7' UNION ALL
SELECT '5','a','2','9' UNION ALL
SELECT '6','a','5','3' UNION ALL
SELECT '7','b','4','6'
-->SQL查询如下:
SELECT * FROM [tb] t ORDER BY (SELECT MAX(f3) FROM tb WHERE f1=t.f1) DESC,f3 DESC
/*
id f1 f2 f3
----------- ---------- ----------- -----------
3 b 3 12
2 b 5 8
7 b 4 6
5 a 2 9
1 a 1 5
6 a 5 3
4 c 3 7
(7 行受影响)
*/
select * from tb order by f1, f3 desc
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (id int,f1 varchar(1),f2 int,f3 int)
insert into #a
select 1,'a',1,5 union all
select 2,'b',5,8 union all
select 3,'b',3,12 union all
select 4,'c',3,7 union all
select 5,'a',2,9 union all
select 6,'a',5,3 union all
select 7,'b',4,6
select a.id,a.f1,a.f2,a.f3 from #a a,
(select row_number() over (order by max(f3) ) as n ,f1 from #a group by f1) b
where a.f1=b.f1
order by b.n desc ,a.f3 desc
drop table #a
(7 row(s) affected)
id f1 f2 f3
----------- ---- ----------- -----------
3 b 3 12
2 b 5 8
7 b 4 6
5 a 2 9
1 a 1 5
6 a 5 3
4 c 3 7
(7 row(s) affected)