sql 本日\本周\本月统计
eg:
1> selectasset_info_src_cd,count(*) from asset wheredatediff(week,asset_setup_dt,getdate())=0
2> group by asset_info_src_cd
eg:
select asset_info_src_cd,count (asset_info_src_cd) from asset wheredatepart(week,asset_setup_dt) =datepart(week,getdate()) group by asset_info_src_cd
本月统计(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())
[SQLServer]
表名为:tableName
时间字段名为:theDate
查询本月的记录
select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本周的记录
select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本季的记录
select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
其中:GETDATE()是获得系统时间的函数。
如:
表:consume_record
字段:consume
date
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月
2月
3月
--按日
select
--按周quarter
select
--按月
select
--按季
select
--指定日期你就看上面的例子变通下呀,无非就是一个聚合函数和Group
select
统计博客聚合用户点击次数
SELECT author, SUM(hits) AS hits
FROM infos
GROUP BY author
ORDER BY hits DESC
统计昨天的记录
SELECT *
FROM infos
WHERE (DATEDIFF(d, pubdate, GETDATE()) = 1)
统计本周的记录
SELECT *
FROM infos
WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART(week,
统计本月的记录:
SELECT *
FROM infos
WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART([month],
eg:
select asset_info_src_cd,count (asset_info_src_cd) from asset wheredatepart(week,asset_setup_dt) =datepart(week,getdate()) group by asset_info_src_cd
eg: select asset_info_src_cd,count(*) from asset where asset_setup_dt >'20150706' and asset_setup_dt <=getdate() group by asset_info_src_cd