我试图设计一个SQL查询，可以返回每一年中最早日期的记录。 假设我有一个如下表:
Date | Category | Value
========== | ======== | =====
2019-01-03 | Apple | 5
2019-01-03 | Orange | 2
2019-01-20 | Apple | 5
2019-01-20 | Orange | 8
2019-02-05 | Apple | 1
2019-02-05 | Peach | 5
2018-01-02 | Apple | 2
2018-01-02 | Orange | 9
2018-05-10 | Apple | 3
2018-05-10 | Orange | 5
2018-07-20 | Apple | 6
2018-07-20 | Orange | 1
我正在尝试生成如下所示的表:
Date | Category | Value
========== | ======== | =====
2019-01-03 | Apple | 5
2019-01-03 | Orange | 2
2018-01-02 | Apple | 2
2018-01-02 | Orange | 9
每年的最早日期将会改变，这意味着我不能简单地按天&； 月。 我尝试使用:
SELECT MIN(Date), *
FROM mytable
GROUPBY YEAR(Date)
但这会导致聚合错误:“Category”在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中
实现这一目标的最佳途径是什么？
一种简单的方法是相关子查询:
select t.*
from mytable t
where t.date = (select min(t2.date)
from mytable t2
where t2.category = t.category and year(t2.date) = year(t.date)
);
您还可以使用row_number():
select t.*
from (select t.*,
row_number() over (partition by category, year(date) order by date) as seqnum
from mytable t
) t
where seqnum = 1
这是一种可能
CREATE TABLE mytable (
[Date] VARCHAR(10),
[Category] VARCHAR(6),
[Value] INTEGER
);
INSERT INTO mytable
([Date], [Category], [Value])
VALUES
('2019-01-03', 'Apple', '5'),
('2019-01-03', 'Orange', '2'),
('2019-01-20', 'Apple', '5'),
('2019-01-20', 'Orange', '8'),
('2019-02-05', 'Apple', '1'),
('2019-02-05', 'Peach', '5'),
('2018-01-02', 'Apple', '2'),
('2018-01-02', 'Orange', '9'),
('2018-05-10', 'Apple', '3'),
('2018-05-10', 'Orange', '5'),
('2018-07-20', 'Apple', '6'),
('2018-07-20', 'Orange', '1');
GO
12 rows affected
SELECT t1.*
FROM mytable t1 INNER JOIN
(SELECT MIN([Date]) mindate,[Category]
FROM mytable
GROUP BY
[Category], YEAR([Date])) t2
ON t1.[Category] = t2.[Category] AND t1.[Date] = t2.mindate
;
GO
Date | Category | Value :--------- | :------- | ----: 2019-01-03 | Apple | 5 2019-01-03 | Orange | 2 2019-02-05 | Peach | 5 2018-01-02 | Apple | 2 2018-01-02 | Orange | 9
db<；>此处小提琴
