Oracle SQL分组/排序 [英] Oracle SQL Grouping/Ordering

问题描述

假设我有以下数据：

  YEAR | PLACE 
 1984 |某处
 1983 |工作
 1985 |某处
 1982 |主页
 1984 |工作
 1983 |主页
 1984 |某处
  

如何获得将所有PLACE列值保持在一起并按YEAR列...所以我要找的结果是：

  YEAR | PLACE 
 1982 |主页
 1983 |主页
 1983 |工作
 1984 |工作
 1984 |某处
 1984 |某处
 1985 |某处
  

谢谢。

编辑：

只是为了说明一些问题的答案......假设我将以下数据添加到我的原始数据中：

  1981 |某处
  

现在结果应该是：

  YEAR |地点
 1981 |某处
 1984 |某处
 1984 |某处
 1985 |某处
 1982 |主页
 1983 |主页
 1983 |工作
 1984 |工作
  

谢谢。

解决方案

  SELECT 
年，
地方
 FROM 
表格名称
 ORDER BY 
 MIN（年份）OVER（按位置划分），
地方，
年份; 
  

测试： SQLfiddle.com

I'm looking for some help in writing an Oracle SQL statement to accomplish the following ...

Let's say I have the following data:

YEAR | PLACE
1984 | somewhere
1983 | work
1985 | somewhere
1982 | home
1984 | work
1983 | home
1984 | somewhere

How can I get a result that keeps all the PLACE column values together and orders it by the YEAR column ... so the result I'm looking for is:

YEAR | PLACE
1982 | home
1983 | home
1983 | work
1984 | work
1984 | somewhere
1984 | somewhere
1985 | somewhere

Thanks.

EDIT:

Just to illustrate answers to some of the questions asked ... let's say I add the following data to my original data:

1981 | somewhere

Now the result should be:

YEAR | PLACE
1981 | somewhere
1984 | somewhere
1984 | somewhere
1985 | somewhere
1982 | home
1983 | home
1983 | work
1984 | work

Thanks.

解决方案

You can achieve this with a window aggregate function:

SELECT 
    year, 
    place
FROM 
    tablename
ORDER BY
    MIN(year) OVER (PARTITION BY place),
    place,
    year ;

Tested at: SQLfiddle.com

这篇关于Oracle SQL分组/排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！