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

Question

我有一张像这样的表

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

，我想知道一行中的最新 3 个日期

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)

Answer 1

虽然我不确定如何将它们放入单行，但您可以从以下开始：

SELECT * FROM Test ORDER BY f1 DESC LIMIT 3

这应该会给您一个类似的结果：

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

不过，将它们放入单行可能会有点困难......

Answer 2

在 sql server 中，您可以执行select top 3 * from Test order by f1 desc。其他 DBMS 也有类似的可能性，例如 MySql 的 limit、Oracle 的 rownum 等。

Answer 3

至少在 SQL Server 上，您可以结合使用 ORDER BY、TOP 和 PIVOT 来实现此目的。似乎许多其他答案都忽略了结果“全部在一行”的需要。

Answer 4

在 T-SQL 中（这将为您提供前三个日期，即使它们都是相同的值）

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]

在 SQL Server 2005 中尝试此操作

--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

Answer 5

您想要做的事情称为数据透视表，这里有一篇关于如何执行此操作的文章：

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

也，如果你使用 Oracle，我会查看分析函数，特别是 OVER PARTITION BY

Answer 6

这又如何呢？

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 不一定按特定顺序（根据上面的示例输出）。但这可以通过编程来实现。好的一面是，您可以在 GROUP BY 之前插入 WHERE 子句，并且可以按 T1.id 进行搜索。

Answer 7

您可以通过旋转表格来获取连续前 3 个日期，如果您希望在旋转后将它们放在一列中，则可以将日期连接起来。

编辑 ：
下面是一个查询，用于对表进行透视并提供连续的最新 3 个日期。但要进行透视，您需要了解表中可用的数据。我想既然我们正在查询最新的 3 个日期，我们将不知道围绕日期列旋转的确切值。首先，我将最新的 3 个日期查询到临时表中。然后对 row_number 1、2 和 3 运行透视以获取连续的最新 3 个日期。

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

现在，在 row_number 列上进行透视 -

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