要求:需要对上表按季度汇总销售额
1、datepart函数方法
1.1、生成季度
select 日期,datepart("q",日期) as 季度 from [sheet10$]
1.2、按季度汇总
select datepart("q",日期) as 季度,
sum(销售额) as 销售额
from [sheet10$]
group by datepart("q",日期)
2、Format函数方法
select format(日期,"q") as 季度,
sum(销售额) as 销售额
from [sheet10$]
group by format(日期,"q")
3、int函数运算方法
select int((month(日期)-1)/3)+1 as 季度,
sum(销售额) as 销售额
from [sheet10$]
group by int((month(日期)-1)/3)+1
4、Datepart函数和Format函数都是直接根据日期返回季度(Quarter),而int取整函数则通过运算得出季度,最后都用group by进行分组。
Datepart函数和Format函数都是常常出现的函数,尤其是Format函数。
Datepart函数几个常见例子为:
Select DATE() as 今天日期,
Datepart("y",DATE()) AS 今年第几天,
Datepart("d",DATE()) AS 本月第几天,
Datepart("w",DATE()) AS 本周第几天,
Datepart("ww",DATE()) AS 今年第几周
Datepart函数第一参数参考如下表:
Format函数几个常见例子为:
select date() as 今天日期,
format(date(),"q") as 今年第几季度,
format(date(),"w") as 本周第几天,
format(date(),"ww") as 今年第几周
select date() as 今天日期,
format(date(),"long date") as 长日期,
format(date(),"medium date") as 中日期,
format(date(),"short date") as 短日期,
format(date(),"long time") as 长时间,
format(date(),"medium time") as 中时间,
format(date(),"short time") as 短时间,
format(date(),"c") as 标准日期
select date() as 今天日期,
format(date(),"yyyy") as 长年,
format(date(),"yy") as 短年,
format(date(),"mmm") as 英文月份,
format(date(),"mm") as 中文月份,
format(date(),"d") as 日
format函数第二参数参考如下表:
在对数据格式化方面Format函数还有更多的应用,这里不一一枚举。
閱讀更多 套路Excel 的文章