在不使用 CTE 的情况下在结果集中按列获取分组

Get Group By Column in result set without using CTE(在不使用 CTE 的情况下在结果集中按列获取分组)

问题描述

这是以下查询

select CAST(DepositeDate AS DATE) as DepositeDate,SUM(TotalAmount) as Amount
from Payment
group by DepositeDate 

我得到以下结果.

DepositeDate | Amount | 
2021-04-30   | 50     | 
2021-04-30   | 50     | 
2021-04-26   | 75     | 
2021-04-11   | 30     | 
2021-04-11   | 30     | 

但我需要以下结果

DepositeDate | Amount | 
2021-04-30   | 100    | 
2021-04-26   | 75     | 
2021-04-11   | 60     |  

这可以不使用 CTE 吗?

Is this possible without using CTE?

推荐答案

显然，您的 DepositeDate 列有一个时间组件.重复GROUP BY中的表达式:

Apparently, your DepositeDate column has a time component. Repeat the expression in the GROUP BY:

select CAST(DepositeDate AS DATE) as DepositeDate,
       SUM(TotalAmount) as Amount
from Payment
group by CAST(DepositeDate AS DATE) 
order by CAST(DepositeDate AS DATE);

SQL Server 无法识别 GROUP BY 中的别名.所以 DepositeDate 总是会引用在 FROM 子句中定义的列，而不是 select 中的表达式.

SQL Server does not recognize aliases in the GROUP BY. So DepositeDate is always going to refer to a column defined in the FROM clause and not the expression in the select.

这篇关于在不使用 CTE 的情况下在结果集中按列获取分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！