我有一张桌子,比如说有结构的“记录”:
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