求统计每周记录数的SQL语句

发布网友发布时间：2022-04-10 09:49

共2个回答

懂视网时间：2022-04-10 14:10

按mySql语法统计按周,月,季,年。income为合计的价格字段，createDate为交易时间。 select sum(income)as revenue,week(createDate) as week,month(createDate) as month, quarter(createDate)as quarter,year(createDate)as year from employee where year(createDate) >= ‘2006‘ group by week(createDate),month(createDate),quarter(createDate),year(createDate) --sqlserver2005语法统计按周,月,季,年。 --按日 --select sum(price),day([date]) from table_name where year([date]) = ‘2006‘ group by day([date]) --按周quarter select sum(price),datename(week,price_time) from ble_name where year(price_time) = ‘2008‘ group by datename(week,price_time) --按月 select sum(price),month(price_time) from ble_name where year(price_time) = ‘2008‘ group by month(price_time) --按季 select sum(price),datename(quarter,price_time) from ble_name where year(price_time) = ‘2008‘ group by datename(quarter,price_time) --按年 select sum(price),year(price_time) from ble_name where year(price_time) >= ‘2008‘ group by year(price_time)

用sql语句按周、按月、按季、按年统计

标签：时间 div mon body where style mysql serve created

热心网友时间：2022-04-10 11:18

declare
@weeknum
int,@year
int,@month
int;--这里是周数、年、月
declare
@weekday
int,@starttime
datetime,@endtime
datetime,@timechar
varchar(20);--星期几、一周开始、一周结束、当月第一天字符串
declare
@datepart
int;--需要查询的周首天与月首天的时间差
--赋值2010年3月
第5周
select
@year=2010,@month=3,@weeknum=5;
set
@timechar=cast(@year
as
varchar)+'-'+cast(@month
as
varchar)+'-1'
--拼接出这个月的第一天
select
@timechar
select
@weekday=datepart(weekday,@timechar)
set
@datepart=(@weeknum-2)*7+(8-@weekday)
select
@starttime=dateadd(dd,@datepart,@timechar)
select
@endtime=dateadd(dd,7,@starttime)
--消除到上个月
及
下个月的出入部分
if
(@starttime<@timechar)
set
@starttime=@timechar
if
(@endtime>dateadd(mm,1,@timechar))
set
@endtime=dateadd(mm,1,@timechar)
select
@weeknum,@starttime,@endtime
--然后就是使用@starttime,@endtime
select
count(*)
from
tb
where
时间>=@starttime
and
时间<@endtime