锐客网

  1. 首页 > 睿知 > it技术 > >

怎么按季度分组MySQL mysql按月分组

IT技术gis

如何在mysql中group by week以周一作为一周的开始, 使用mysql week行数模式5:
作为周划分的标准, 比如20170101是周天, week(20170101, 5) = 0,
分WEEK_IN_MONTH, 和WEEK_IN_YEAR两种情况进行分组:
WEEK_IN_YEAR:
# by sleest 2017/03/29 按每个日期所在一年中的第几周分组汇总
SELECT WEEK(MY_DATE, 5)+1 AS WEEK_OF_YEAR, COUNT(1) AS COUNT,
GROUP_CONCAT(MY_DATE) AS INCLUDE_DATE
FROM (SELECT '2017-01-01' AS MY_DATE
UNION ALL
SELECT '2017-02-08'
UNION ALL
SELECT '2017-02-03'
UNION ALL
SELECT '2017-02-01'
UNION ALL
SELECT '2017-01-21') TMP
GROUP BY WEEK(MY_DATE, 5) + 1;
结果:
WEEK_IN_MONTH:
# by sleest 2017/03/29 按每个日期所在每个月的第几周分组汇总
SELECT WEEK(MY_DATE, 5) - WEEK(DATE_SUB(MY_DATE, INTERVAL DAYOFMONTH(MY_DATE) - 1 DAY), 5) + 1 AS WEEK_OF_MONTH,
COUNT(1) AS COUNT,
GROUP_CONCAT(MY_DATE) AS INCLUDE_DATE
FROM (SELECT '2017-01-01' AS MY_DATE
UNION ALL
SELECT '2017-02-08'
UNION ALL
SELECT '2017-02-03'
UNION ALL
SELECT '2017-02-01'
UNION ALL
SELECT '2017-01-21') TMP
GROUP BY WEEK(MY_DATE, 5) - WEEK(DATE_SUB(MY_DATE, INTERVAL DAYOFMONTH(MY_DATE) - 1 DAY), 5) + 1
结果:
sql server如何按季度分组统计所有的数据和按月份组的原理是一样的吧！
按月分组
按季度分组和按月分组的区别应该就是时间段的区别吧！
select case whenmonth(date)=1 or month(date) =2
or month(date)=3then '一季度'
whenmonth(date)=4 or month(date) =5
or month(date)=6then '2季度'
whenmonth(date)=7 or month(date) =8
or month(date)=9then '3季度'
whenmonth(date)=10 or month(date) =11
or month(date)=12then '4季度'
else '' end ,sum(数量)
from table
group by
case whenmonth(date)=1 or month(date) =2
or month(date)=3then '一季度'
whenmonth(date)=4 or month(date) =5
or month(date)=6then '2季度'
whenmonth(date)=7 or month(date) =8
or month(date)=9then '3季度'
whenmonth(date)=10 or month(date) =11
or month(date)=12then '4季度'
else '' end
已知 ，数据库两个字段，年，月 。现在要按照季度分组查询，怎么写sql语句？先case when将月份变成季度，然后再group by即可，SQL如下：
select year, quarter, count(1)
from (
select year,
case
when month4 then 1
when month7 then 2
when month10 then 3
else 4
end as quarter
from table
) as result
group by year, quarter
mysql 怎么查询now() 要显示年份 和季度数您好 ， 一、年度查询
查询 本年度的数据
SELECT *
FROM blog_article
WHERE year( FROM_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))
二、查询季度数据
查询数据附带季度数
SELECT ArticleId, quarter( FROM_UNIXTIME( `BlogCreateTime` ) )
FROM `blog_article`
其他的同前面部分：查询 本季度的数据
SELECT *
FROM blog_article
WHERE quarter( FROM_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))
三、查询月度数据
【怎么按季度分组MySQL mysql按月分组】本月统计(MySQL)
select * from booking where month(booking_time) =
month(curdate()) and year(booking_time) = year(curdate())
本周统计(MySQL)
select * from spf_booking where month(booking_time) =
month(curdate()) and week(booking_time) = week(curdate())
四、时间段
N天内记录
WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) = N
当天的记录
where date(时间字段)=date(now())
或
where to_days(时间字段) = to_days(now());
查询一周：
select * from tablewhere DATE_SUB(CURDATE(), INTERVAL 7 DAY) = date(column_time);

推荐阅读


上一篇：直播用什么网络电视好，直播用什么网好不卡

下一篇：excel如何进行差异性分析，差异性分析怎么做