代码之家 › 专栏 › 技术社区 › Maestro1024

如何从表中获取几个最高值?

select sql-server sql

Maestro1024 · 技术社区 · 14 年前

我有一张像这样的桌子

id  f1
--------------
1   2000-01-01
1   2001-01-01
1   2002-01-01
1   2003-01-01

我想说最近三次约会

CREATE TABLE Test
(
  id INT NOT NULL,
  f1 DATETIME NOT NULL,
)

INSERT INTO Test (id, f1) VALUES (1, '1/1/2000')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2001')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2002')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2003')

SELECT T1.* FROM Test as T1

尝试了一些像

         SELECT T1.*,T2.* 
           FROM Test AS T1
LEFT OUTER JOIN Test AS T2 ON T1.id = T2.id AND (T2.f1 > T1.f1)

7 回复 | 直到 14 年前

Klaus Byskov Pedersen 14 年前

select top 3 * from Test order by f1 desc limit rownum

Jonathan 14 年前

SELECT * FROM Test ORDER BY f1 DESC LIMIT 3

id  f1
1   2003-01-01
1   2002-01-01
1   2001-01-01

Michael Goldshteyn 14 年前

ORDER BY TOP PIVOT

Chris Hayes 14 年前

with TestWithRowNums(f1, row_num) as
(
select f1, row_number() over(order by [f1] desc) as row_num from test
)
select
(select [f1] from TestWithRowNums where row_num = 1) as [Day 1],
(select [f1] from TestWithRowNums where row_num = 2) as [Day 2],
(select [f1] from TestWithRowNums where row_num = 3) as [Day 3]

with TestWithRankNums(f1, rank_num) as
(
select f1, dense_rank() over(order by [f1] desc) as rank_num from test
)
select
(select top 1 [f1] from TestWithRankNums where rank_num = 1) as [Day 1],
(select top 1 [f1] from TestWithRankNums where rank_num = 2) as [Day 2],
(select top 1 [f1] from TestWithRankNums where rank_num = 3) as [Day 3]

--to get top three values even if they are the same
select [1] as Day1, [2] as Day2, [3] as Day3 from 
(select top 3 f1, row_number() over(order by [f1] desc) as row_num from test) src
pivot
(
max(f1) for row_num in([1], [2], [3])
) as pvt
--to get top three distinct values
select [1] as Day1, [2] as Day2, [3] as Day3 from 
(select f1, dense_rank() over(order by [f1] desc) as row_num from test) src
pivot
(
max(f1) for row_num in([1], [2], [3])
) as pvt

Roman Goyenko 14 年前

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740

Yanick Rochon 14 年前

SELECT T1.f1 as "date 1", T2.f1 as "date 2", T3.f1 as "date 3"
  FROM (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 1) AS T1,
      (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 1, 1) AS T2,
      (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 2, 1) AS T3
;

+------------+------------+------------+
| date 1     | date 2     | date 3     |
+------------+------------+------------+
| 2003-01-01 | 2002-01-01 | 2001-01-01 |
+------------+------------+------------+

JOIN

SELECT T1.id, 
       T1.f1 as "date 1", 
       T2.f1 as "date 2", 
       T3.f1 as "date 3"
  FROM `date_test` as T1
  LEFT JOIN (SELECT * FROM `date_test` ORDER BY `f1` DESC) as T2 ON (T1.id=T2.id AND T1.f1 != T2.f1)
  LEFT JOIN (SELECT * FROM `date_test` ORDER BY `f1` DESC) as T3 ON (T1.id=T3.id AND T2.f1 != T3.f1 AND T1.f1 != T3.f1)
 GROUP BY T1.id
 ORDER BY T1.id ASC, T1.f1 DESC

+----+------------+------------+------------+
| id | date 1     | date 2     | date 3     |
+----+------------+------------+------------+
| 1  | 2001-01-01 | 2003-01-01 | 2002-01-01 |
+----+------------+------------+------------+

date1 date 2 date 3 WHERE GROUP BY T1.id

pavanred 14 年前

Select Top 3 * into #Temp from Test order by f1 desc

SELECT id,[3] as Latest,[2] as LatestMinus1,[1] as LatestMinus2
FROM (
select ROW_NUMBER() OVER(ORDER BY f1) AS RowId,f1,id from #Temp) AS Src
PIVOT (Max(f1) FOR RowId IN ([1],[2],[3])) AS pvt

Id | Latest                  |LatestMinus1             |LatestMinus2
1  | 2003-01-01 00:00:00.000 | 2002-01-01 00:00:00.000 | 2001-01-01 00:00:00.000

drop table #Temp