表A有三个字段,F1,F2,F3,F4
F1 F2 F3 F4
a 01 01 aaa
a 01 02 bbb
a 02 01 ccc
b 01 01 ddd
b 02 01 eee
b 02 02 fff要求:对F1字段做Group by,取出最大的F2中对应的最大的F3。
结果为
F1 F2 F3 F4
a 02 01 ccc
a 02 02 fff我想到的办法需要做三次查询。
第一次查出最大的F2,第二次按照最大F2查出最大的F3,第三次根据前两次的结果查出其他字段信息。
个人感觉非常的麻烦。不知道有没有好的办法。
F1 F2 F3 F4
a 01 01 aaa
a 01 02 bbb
a 02 01 ccc
b 01 01 ddd
b 02 01 eee
b 02 02 fff要求:对F1字段做Group by,取出最大的F2中对应的最大的F3。
结果为
F1 F2 F3 F4
a 02 01 ccc
a 02 02 fff我想到的办法需要做三次查询。
第一次查出最大的F2,第二次按照最大F2查出最大的F3,第三次根据前两次的结果查出其他字段信息。
个人感觉非常的麻烦。不知道有没有好的办法。
-- Author : htl258(Tony)
-- Date : 2010-04-06 12:58:31
-- 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 2)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([F1] NVARCHAR(10),[F2] NVARCHAR(10),[F3] NVARCHAR(10),[F4] NVARCHAR(10))
INSERT [a]
SELECT 'a','01','01','aaa' UNION ALL
SELECT 'a','01','02','bbb' UNION ALL
SELECT 'a','02','01','ccc' UNION ALL
SELECT 'b','01','01','ddd' UNION ALL
SELECT 'b','02','01','eee' UNION ALL
SELECT 'b','02','02','fff'
GO
--SELECT * FROM [a]-->SQL查询如下:
select * from a t where F4 IN(select top 1 F4 from a where F1=t.F1 order by F2 desc,F3 desc)
/*
F1 F2 F3 F4
---------- ---------- ---------- ----------
a 02 01 ccc
b 02 02 fff(2 行受影响)*/
-- Author : happyflystone
-- Date : 2010-04-06 12:58:06
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([F1] NVARCHAR(1),[F2] NVARCHAR(2),[F3] NVARCHAR(2),[F4] NVARCHAR(3))
Go
INSERT INTO ta
SELECT 'a','01','01','aaa' UNION ALL
SELECT 'a','01','02','bbb' UNION ALL
SELECT 'a','02','01','ccc' UNION ALL
SELECT 'b','01','01','ddd' UNION ALL
SELECT 'b','02','01','eee' UNION ALL
SELECT 'b','02','02','fff'
GO
--Start
;with cte
as
( SELECT
* ,rid = row_number() over (partition by f1 order by f2 desc,f3 desc)
fROM
TA)
select *
from cte where rid = 1--Result:
/*F1 F2 F3 F4 rid
---- ---- ---- ---- --------------------
a 02 01 ccc 1
b 02 02 fff 1
*/
--End
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-06 13:01:18
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([F1] varchar(1),[F2] varchar(2),[F3] varchar(2),[F4] varchar(3))
insert [tb]
select 'a','01','01','aaa' union all
select 'a','01','02','bbb' union all
select 'a','02','01','ccc' union all
select 'b','01','01','ddd' union all
select 'b','02','01','eee' union all
select 'b','02','02','fff'
--------------开始查询--------------------------
SELECT
DISTINCT b.*
FROM tb AS a
CROSS APPLY
(
SELECT TOP 1 * FROM tb WHERE F1=a.F1 order by F2 desc,F3 desc
) AS b
----------------结果----------------------------
/* ---- ---- ---- ----
a 02 01 ccc
b 02 02 fff(2 行受影响)*/
INSERT [a]
SELECT 'a','01','01','aaa' UNION ALL
SELECT 'a','01','02','bbb' UNION ALL
SELECT 'a','02','01','ccc' UNION ALL
SELECT 'b','01','01','ddd' UNION ALL
SELECT 'b','02','01','eee' UNION ALL
SELECT 'b','02','02','fff'
GOselect m.* from
(
select t.* from a t where F2 = (select max(F2) from a where f1 = t.f1)
) m where f3 = (select max(f3) from
(
select t.* from a t where F2 = (select max(F2) from a where f1 = t.f1)
) n where f1 = m.f1 and f2 = m.f2
)
/*
F1 F2 F3 F4
---------- ---------- ---------- ----------
a 02 01 ccc
b 02 02 fff(所影响的行数为 2 行)
*/select m.* from
(
select t.* from a t where not exists (select 1 from a where f1 = t.f1 and f2 > t.F2)
) m where not exists(select 1 from
(
select t.* from a t where not exists (select 1 from a where f1 = t.f1 and f2 > t.F2)
) n where f1 = m.f1 and f2 = m.f2 and f3 > m.f3
)
/*
F1 F2 F3 F4
---------- ---------- ---------- ----------
a 02 01 ccc
b 02 02 fff(所影响的行数为 2 行)
*/
drop table a
INSERT [a]
SELECT 'a','01','01','aaa' UNION ALL
SELECT 'a','01','02','bbb' UNION ALL
SELECT 'a','02','01','ccc' UNION ALL
SELECT 'b','01','01','ddd' UNION ALL
SELECT 'b','02','01','eee' UNION ALL
SELECT 'b','02','02','fff'
GOselect F1 , F2 , F3 , F4 from
(
select t.* , px = (select count(1) from a where f1 = t.f1 and (f2 > t.f2 or (f2 = t.f2 and f3 > t.f3))) + 1 from a t
) m
where px = 1drop table a/*
F1 F2 F3 F4
---------- ---------- ---------- ----------
a 02 01 ccc
b 02 02 fff(所影响的行数为 2 行)
*/
select F1 , F2 , F3 , F4 from
(
select t.* , px = row_number() over(partition by f1 order by f2 desc , f3 desc) from a t
) m
where px = 1
FROM
(SELECT
* ,rid = row_number() over (partition by f1 order by f2 desc,f3 desc)
fROM
TA) TT WHERE TT.rid =1