您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > MySql: Year, Quarter, Month, Day, Hour statistics

MySql: Year, Quarter, Month, Day, Hour statistics

发布时间:2019-12-31 22:27编辑:威尼斯官方网站登录浏览(56)

     

     

    --  统计
    
    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: Year, Quarter, Month, Day, Hour statistics

    关键词: