logo标签列表

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

sqlsql-serverselect
6

I have a table like 

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)
3
为什么必须得都在一行上？只放一列会更容易些。 - Mark Byers
1
我认为将它们作为行返回而不是一个具有多个列的行更有意义。 - dotariel
你需要每个ID的最近三个日期吗？（我注意到您的ID始终为1） - Yanick Rochon
7个回答
5

虽然我不确定如何将它们放在同一行，但你可以从以下方式开始：

SELECT * FROM Test ORDER BY f1 DESC LIMIT 3

这应该会给你一个类似这样的结果：
id  f1
1   2003-01-01
1   2002-01-01
1   2001-01-01

然而，将它们放在一排可能会更加困难...

3

在 SQL Server 中，您可以使用 select top 3 * from Test order by f1 desc 实现此功能。其他数据库管理系统也有类似的可能性，例如 MySql 的 limit、Oracle 的 rownum 等。

这样行不通。这会在一列中给出最新的3个日期。Maestro1024要求在一行中给出最新的3个日期。 - pavanred
2

在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
我正在使用MS SQL，但它不起作用。语法正确吗？还是在MS SQL上无效？ - Maestro1024
@Maestro1024，这只适用于新版本的SQL Server，但它肯定通过了语法检查。 - HLGEM
@Maestro1024，您使用的是哪个版本的MS SQL Server？ - Chris Hayes
2

您可以在SQL Server上使用ORDER BY、TOP和PIVOT的组合来实现此操作。似乎其他答案忽略了结果需要“全部在一行”这一需求。

1

这个怎么样？

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

1
0

您可以通过透视表并在透视后将日期连接起来，以获取连续的前三个日期。

编辑： 这里有一个查询来透视表格并提供最新的三个连续日期。但是，要进行透视，您需要知道表中可用的数据。我想，既然我们正在查询最新的三个日期，我们将不知道围绕日期列透视的确切值。因此，首先，我将最新的三个日期查询到一个临时表中。然后，在行号1、2和3上运行透视，以获得连续的最新三个日期。

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
编辑：在我的回答中添加了一个查询。 - pavanred
分析功能并非所有数据库都支持——SQL Server 2005+、Oracle 9i+、PostgreSQL 8.4+……但 MySQL 不支持。 - OMG Ponies
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接