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

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
--  统计
 
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());
posted @ 2018-08-24 13:01 ®Geovin Du Dream Park™ 阅读(496) 评论(0) 编辑收藏举报
®Geovin Du Dream Park™

why we only heard about haves and have-nots, but we did'nt heard about doers and doer-nots. 人生是一种心境,生活是一种艺术,成功是一种心态,幸福是一种感觉,竞争是一种建构,情感是一种容合.学习是一种成长.

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

公告

搜索

常用链接

我的标签

积分与排名

随笔分类

随笔档案

文章分类

相册

捷为工作室

阅读排行榜

评论排行榜

推荐排行榜

最新评论
