我有一张桌子,比如说有结构的“记录”:

id      date
--      ----
1       2012-08-30
2       2012-08-29
3       2012-07-25

我需要在PostgreSQL中编写一个SQL查询来获取每个月最小日期的记录id。
month    record_id
-----    ---------
8           2
7           3

如我们所见,2012-08-29我试过这样的东西,
SELECT
   EXTRACT(MONTH FROM date) as month,
   record_id,
   MIN(date)
FROM Records
GROUP BY 1,2

但它显示了3张唱片。
有人能帮忙吗?

最佳答案

如果有重复的最短日期,则返回倍数:

Select
  minbymonth.Month,
  r.record_id
From (
  Select
    Extract(Month From date) As Month,
    Min(date) As Date
  From
    records
  Group By
    Extract(Month From date)
  ) minbymonth
    Inner Join
  records r
    On minbymonth.date = r.date
Order By
  1;

或者如果你有CTE
With MinByMonth As (
  Select
    Extract(Month From date) As Month,
    Min(date) As Date
  From
    records
  Group By
    Extract(Month From date)
  )
Select
  m.Month,
  r.record_id
From
  MinByMonth m
    Inner Join
  Records r
    On m.date = r.date
Order By
  1;

http://sqlfiddle.com/#!1/2a054/3

08-28 02:42
查看更多