日期:2014-05-17 浏览次数:20546 次
CREATE TABLE [dbo].[TestTable](
[c1] [varchar](10) NULL,
[c2] [int] NULL,
[c3] [numeric](10, 1) NULL
)
select c1,c2,max(c3) from TestTable group by c1
SELECT *
FROM testtable a
WHERE EXISTS (SELECT 1 FROM (
select c1,max(c3) c3 from TestTable group by c1 )
b WHERE a.c1=b.c1 AND a.c3=b.c3)
CREATE TABLE [dbo].[TestTable](
[c1] [varchar](10) NULL,
[c2] [int] NULL,
[c3] [numeric](10, 1) NULL
)
INSERT TestTable
SELECT 'A',5,5.4 UNION ALL
SELECT 'A',6,2.4 UNION ALL
SELECT 'B',5,6.5 UNION ALL
SELECT 'B',6,7.2
--楼主的意思我认为是取c3列最大值的所有信息,而对于c2的大小不考虑
SELECT c1,c2,c3
FROM TestTable T1 WHERE NOT EXISTS (SELECT 1 FROM TestTable T2 WHERE T2.c1=T1.c1 AND T2.c3>T1.c3)
DROP TABLE TestTable
(4 行受影响)
c1 c2 c3
---------- ----------- ---------------------------------------
A 5 5.4
B 6 7.2
(2 行受影响)
SELECT *
FROM (SELECT *,
Row_number()OVER (partition BY c1 ORDER BY c3DESC ) AS id
FROM test_table) a
WHERE id <= 1