使用T-SQL：按日期排序，然后分组？

Question

假设我有一个数据库表，看起来像这样：

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W

有没有一种简单的方法按照start_date降序排序，然后对于每个城市将它们按最新的start_date分组？例如：

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
      9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
      6 James            70060 1999-09-06 00:00:00.000 Toronto    N
      3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
      8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
      5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
      2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
      7 Alison           90620 2000-08-07 00:00:00.000 New York   W
      4 Linda            40620 1997-11-04 00:00:00.000 New York   N
      1 Jason            40420 1994-02-01 00:00:00.000 New York   W

感谢您的回复。

- David

Answer 1

在SQL Server 2005或更新版本中可以这样写：

select 
  * 
from
  (select *,max(start_date) over(partition by city) max_date from tablename) alias
order by max_date desc, start_date desc

- LukLed

---

别忘了按照 start_date desc 和 max_date desc 的顺序排序。 - Peter Radocchia

Answer 2

SELECT yourTable.*
  FROM yourTable INNER JOIN
      (SELECT city, MAX(start_date) AS max_city_date 
         FROM yourTable 
        GROUP BY city) max_dates
      ON yourTable.city = max_dates.city
ORDER BY max_dates.max_city_date DESC, yourTable.city, yourTable.start_date DESC

ORDER BY子句中的yourTable.city确保了如果两个城市具有相同的max_city_date，则按城市进行一致的分组。

- Heinzi

Answer 3

将查询与自身连接，并按城市名称进行分组。然后，您可以在ORDER BY子句中使用城市的最大开始日期。

select c1.*
from cities c1
left join cities c2 on c1.city = c2.city
group by c1.id, c1.name, c1.salary, c1.start_date, c1.city, c1.region
order by max(c2.start_date) desc, c1.city, c1.start_date desc

- Andomar

Answer 4

问题不是很清楚，但这将生成您列出的表格：

select *
from MyTable p
order by 
    (SELECT MAX(start_date) AS max_city_date FROM MyTable WHERE city=p.city) desc, 
    city, 
    start_date desc

这是您想要的吗？如果您能提供更多指导，那就更好了。

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ----------
9           Mary       60020       2002-06-09 00:00:00.000 Toronto    W
6           James      70060       1999-09-06 00:00:00.000 Toronto    N
3           Celia      24020       1996-12-03 00:00:00.000 Toronto    W
8           Chris      26020       2001-07-08 00:00:00.000 Vancouver  N
5           David      80026       1998-10-05 00:00:00.000 Vancouver  W
2           Robert     14420       1995-01-02 00:00:00.000 Vancouver  N
7           Alison     90620       2000-08-07 00:00:00.000 NewYork    W
4           Linda      40620       1997-11-04 00:00:00.000 NewYork    N
1           Jason      40420       1994-02-01 00:00:00.000 NewYork    W

- Nestor

Answer 5

试试这个：

SELECT * 
FROM
    (
    SELECT 
        *,
        (SELECT MAX(start_date) FROM cities c2 WHERE c1.city = c2.city) AS latest_start_date
    FROM cities c1
    )
ORDER BY latest_start_date DESC, start_date DESC

内部查询将会给你类似这样的结果：

ID          name       salary      start_date              city       region lastest_start_date
----------- ---------- ----------- ----------------------- ---------- ------ -------------------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W      2000-08-07 00:00:00.000
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N      2001-07-08 00:00:00.000
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W      2002-06-09 00:00:00.000
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N      2000-08-07 00:00:00.000
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W      2001-07-08 00:00:00.000
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N      2002-06-09 00:00:00.000
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W      2000-08-07 00:00:00.000
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N      2001-07-08 00:00:00.000
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W      2002-06-09 00:00:00.000

- Lukasz Lysik