如何SQL查询有事务的月份的最后一天

Question

我的数据集如下：

+------+-------+-----+--------+
| Year | Month | Day | Amount |
+------+-------+-----+--------+
| 2019 |    01 |  01 |     10 |
| 2019 |    01 |  15 |     30 |
| 2019 |    01 |  29 |     40 |
| 2019 |    02 |  02 |     50 |
| 2019 |    02 |  22 |     60 |
| 2019 |    03 |  11 |     70 |
| 2019 |    03 |  31 |     80 |
+------+-------+-----+--------+

我只想看看每个月有交易的最后一个记录日。

我的首选结果应该是这样的：

+------+-------+--------+
| Year | Month | Amount |
+------+-------+--------+
| 2019 |    01 |     40 |
| 2019 |    02 |     60 |
| 2019 |    03 |     80 |
+------+-------+--------+

Answer 1

对于Year和Month的每个组合，您需要获得最大的Day和Amount值：

SELECT Year, Month, max(Day) as Day, max(Amount) as Amount
FROM t
GROUP BY Year, Month;

请注意，出现在SELECT子句中但未出现在GROUP BY子句中的每一列都必须进行聚合(此处为max(。

也就是说，假设Amount对应于每天的总数，这就是您的示例所建议的。

如果您的表格每天包含多个Amount，那么您还需要汇总每天的金额。我会用这样的东西：

SELECT Year, Month, max(Day) as Day, max(Amount) as Amount
FROM (
SELECT Year, Month, Day, sum(Amount) as Amount 
FROM t
GROUP BY Year, Month, Day
) as tmp
GROUP BY Year, Month;

测试我(在你的例子中再添加一个数量(

或者：

SELECT Year, Month, Day, sum(Amount) as Amount
FROM (
SELECT *, rank() over(partition by Year, Month order by Day desc) as r
FROM t
) as tmp
WHERE r = 1
GROUP BY Year, Month, Day;

请注意，您希望在这里使用rank()而不是row_number()，因为您需要为平局(同一天(提供相同的等级标识符。

当然，如果你愿意，你可以用包装上面的任何查询

SELECT Year, Month, Amount
FROM (<query>) as q;

去掉日栏。

Answer 2

您可以使用行号来实现这一点：

SELECT [Year], [Month], [Amount]
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY CONCAT(YEAR,MONTH) ORDER BY DAY DESC) rn, *
FROM table) t
WHERE rn = 1

Answer 3

一种方法是相关的子查询：

select t.*
from t
where t.day = (select max(t2.day)
from t t2
where t2.year = t.year and t2.month = t.month
);

另一种常见的方法使用row_number():

select t.*
from (select t.*,
row_number() over (partition by year, month order by day desc) as seqnum
from t
) t
where seqnum = 1;

Answer 4

您可以使用以下内容：

选择ROW_NUMBER((OVER(按年份划分，按日期排序(为rn*从您的桌子质量rn=1

Answer 5

使用ROW_NUMBER((函数：

with cte_order as (select year, month, amount,row_number() over(partition by month order by day desc) as identity_row from test1)

从cte_order中选择年、月、金额，其中identity_row=1；

使用QUALIFY((函数：

select year, month, amount, day, row_number() over(partition by month order by day desc) as identity_row from test1 qualify identity_row = 1;

Answer 6

select year,month,amount from(SELECT year,month,max(day),amount FROM t GROUP BY year,month);