當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MYSQL关于日期统计常用的SQL语句

發布時間：2023/12/10 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 MYSQL关于日期统计常用的SQL语句小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

--? 統計

select count(*) as '當天記錄數' from web_product where date(p_createtime) = curdate();

select count(*) as '當天記錄數' from web_product where to_days(p_createtime) = to_days(now());

SELECT count(*) as '昨天記錄數'? FROM web_product WHERE TO_DAYS( NOW( ) ) - TO_DAYS( p_createtime) <= 1;

-- 前一天

select count(*) as '前一天記錄數' from web_product where date(p_createtime) = date_sub(curdate(),interval 1 day);

select count(*) as '本周記錄數' from web_product where date(p_createtime) >= date_sub(curdate(),interval 7 day)? ?

and date(p_createtime) <=? date_sub(curdate(),interval 1 day);

SELECT count(*) as '7天的記錄數' FROM web_product where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(p_createtime);

-- 查詢近30天的記錄

SELECT * FROM web_product where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(p_createtime);

-- 查詢本月的記錄

SELECT * FROM web_product WHERE DATE_FORMAT(p_createtime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');

-- 查詢上一月的記錄

SELECT * FROM web_product WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(p_createtime,'%Y%m'))=1;

-- 查詢本季度數據

select * from web_product where QUARTER(p_createtime)=QUARTER(now());

-- 查詢上季度數據

select * from web_product where QUARTER(p_createtime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查詢本年數據

select * from web_product? where YEAR(p_createtime)=YEAR(NOW());

-- 查詢上年數據

select * from web_product where year(p_createtime)=year(date_sub(now(),interval 1 year));

-- 查詢當前這周的數據

SELECT * FROM web_product WHERE YEARWEEK(date_format(p_createtime,'%Y-%m-%d')) = YEARWEEK(now());

-- 查詢上周的數據

SELECT * FROM web_product WHERE YEARWEEK(date_format(p_createtime,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 查詢當前月份的數據

select * from web_product? ?where date_format(p_createtime,'%Y-%m')=date_format(now(),'%Y-%m');

-- 查詢距離當前現在6個月的數據

select p_name,p_createtime from web_product where p_createtime between date_sub(now(),interval 6 month) and now();

-- 按年匯總，統計：

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%Y');

select date_format(p_createtime, '%Y') as 'year',count(*) as sheets from web_product group by date_format(p_createtime, '%Y');

select DATE_FORMAT(p_createtime,'%Y') years,sum(duration) dur from web_product tv where 1=1 GROUP BY years ORDER BY years desc;

select DATE_FORMAT(p_createtime,'%Y') years,count(*) as sheets? from web_product? where 1=1 GROUP BY years ORDER BY years desc;

SELECT DATE_FORMAT(p_createtime,'%Y') years,COUNT(*) COUNT FROM web_product GROUP BY years;

SELECT year(p_createtime) as 'yearname',count(*) as'sheet' FROM `web_product` group by? yearname;

SELECT count(*), year(p_createtime) yearname? FROM `web_product`? group by? yearname;

SELECT year(p_createtime) yearname? FROM `web_product`;

SELECT? DISTINCT(year(p_createtime)) yearname? FROM `web_product`;

SELECT? COUNT(DISTINCT(year(p_createtime))) yearname? FROM `web_product`;

SELECT year(Addtime) as 'yearname',count(*) as'sheet' FROM `duwebstat` group by? yearname;

SELECT? COUNT(DISTINCT(year(Addtime))) yearname? FROM `duwebstat`;

-- 按月匯總，統計：

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m');

select date_format(p_createtime, '%Y-%m') as 'month',count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m');

select DATE_FORMAT(p_createtime,'%Y%m') months,count(*) as sheets from web_product? where 1=1 GROUP BY months ORDER BY months desc;

SELECT DATE_FORMAT(p_createtime,'%Y%m') months,COUNT(*) COUNT FROM web_product GROUP BY months;

SELECT year(p_createtime) as 'yearname',month(`p_createtime`) as 'monthname',count(*) as'sheet' FROM `web_product` group by? yearname,monthname;

SELECT year(Addtime) as 'yearname',month(`Addtime`) as 'monthname',count(*) as'sheet' FROM `duwebstat` group by? yearname,monthname;

SELECT? count(DISTINCT(concat(cast(year(Addtime) as char(50)),cast(month(Addtime) as char(50)))))? FROM duwebstat;

select DATE_FORMAT(Addtime,'%Y-%m') months,count(*) as sheets from duwebstat? where 1=1 GROUP BY months ORDER BY months desc;

-- 按季度匯總，統計：

select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by concat(date_format(p_createtime, '%Y'),FLOOR((date_format(p_createtime, '%m')+2)/3));

select count(*) as sheets from web_product group by concat(date_format(p_createtime, '%Y'),FLOOR((date_format(p_createtime, '%m')+2)/3));

select concat(date_format(p_createtime,'%Y'),FLOOR((date_format(p_createtime, '%m')+2)/3)) quarters,sum(duration) dur from web_product? where 1=1? GROUP BY quarters ORDER BY quarters desc;

select concat(date_format(p_createtime,'%Y'),FLOOR((date_format(p_createtime, '%m')+2)/3)) quarters,count(*) as sheets? from web_product? where 1=1? GROUP BY quarters ORDER BY quarters desc;

SELECT id, year(p_createtime),quarter(`p_createtime`) FROM `web_product`;

SELECT year(p_createtime) as 'yearname',quarter(`p_createtime`) as 'quartername',count(*) as'sheet' FROM `web_product` group by? yearname,quartername;

SELECT? DISTINCT(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50))))? FROM web_product;

SELECT? count(DISTINCT(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50)))))? FROM web_product;

select CAST(122 as CHAR);

select now();

select quarter(now());

SELECT CAST(123 AS CHAR);

select concat(DATE_FORMAT(now(),'%Y'),cast(quarter(now()) as char(20)));

SELECT year(Addtime) as 'yearname',quarter(`Addtime`) as 'quartername',count(*) as'sheet' FROM `duwebstat` group by? yearname,quartername;

SELECT? COUNT(DISTINCT(year(Addtime))) yearname? FROM `duwebstat`;

SELECT? count(DISTINCT(concat(cast(year(Addtime) as char(50)),cast(quarter(Addtime) as char(50)))))? FROM duwebstat;

--? 按周統計

select DATE_FORMAT(p_createtime,'%Y%u') weeks,count(*) as sheets from web_product where 1=1 GROUP BY weeks ORDER BY weeks desc;

select DATE_FORMAT(p_createtime,'%Y-%u') weeks,count(*) as sheets from web_product where 1=1 GROUP BY weeks ORDER BY weeks desc;

SELECT DATE_FORMAT(p_createtime,'%Y%u') weeks,COUNT(*) COUNT FROM web_product GROUP BY weeks;

SELECT DATE_FORMAT(Addtime,'%Y-%u') weeks,COUNT(*) COUNT FROM duwebstat GROUP BY weeks;

select DISTINCT(DATE_FORMAT(p_createtime,'%Y-%u')) from web_product;

SELECT year(p_createtime) yearname,week(p_createtime) weeks,COUNT(*) COUNT FROM web_product GROUP BY weeks,yearname;

SELECT year(Addtime) yearname,week(Addtime) weeks,COUNT(*) COUNT FROM duwebstat GROUP BY weeks,yearname;

select DATE_FORMAT(Addtime,'%Y%u') weeks,count(*) as sheets from duwebstat where 1=1 GROUP BY weeks ORDER BY weeks desc;

select count(DISTINCT(DATE_FORMAT(Addtime,'%Y-%u'))) from duwebstat;

-- 按日統計

-- https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

SELECT DATE_FORMAT(p_createtime,'%Y%m%d') days,COUNT(*) COUNT FROM web_product GROUP BY days;

SELECT DATE_FORMAT(p_createtime,'%Y-%m-%d') days,COUNT(*) COUNT FROM web_product GROUP BY days;

SELECT DATE_FORMAT(Addtime,'%Y-%m-%d') days,COUNT(*) as sheet FROM duwebstat GROUP BY days;

select count(DISTINCT(DATE_FORMAT(Addtime,'%Y-%m-%d'))) from duwebstat;

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

-- 按小時：Hour

select date_format(p_createtime, '%Y-%m-%d %H'),count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H');

select date_format(p_createtime, '%Y-%m-%d %H'),count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H') limit 0,30;

select date_format(Addtime, '%Y-%m-%d %H') as hours,count(*) as sheet from duwebstat group by date_format(Addtime, '%Y-%m-%d %H');

select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H ');

-- 查詢本年度的數據:

SELECT * FROM web_product WHERE year(FROM_UNIXTIME(p_createtime)) = year(curdate());

-- 查詢數據附帶季度數:

SELECT id, quarter(FROM_UNIXTIME(p_createtime)) FROM web_product;

-- 查詢本季度的數據:

SELECT * FROM web_product WHERE quarter(FROM_UNIXTIME(p_createtime)) = quarter(curdate());

-- 本月統計:

select * from web_product where month(p_createtime) = month(curdate()) and year(p_createtime) = year(curdate());

-- 本周統計:

select * from web_product where month(p_createtime) = month(curdate()) and week(p_createtime) = week(curdate());

總結

以上是生活随笔為你收集整理的MYSQL关于日期统计常用的SQL语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： stylus之条件(Conditiona
下一篇：【工具】动软代码生成器连接数据库