id mobcount
1 2
2 1
3 5
4 3
.............
其中字段mobcount表示手机号码个数
表二：Jp_SMSInfo
id bookid submittime
1 1 2008-5-10 23:03:57
2 2 2008-5-11 1:51:21
3 3 2008-5-11 9:43:32
4 4 2008-5-12 13:58:55
.................................
其中字段bookid表示表Jp_MobBook的主键，submittime字段表示号码提交日期
现要实现如下统计结果列表：
日期 提交条数
10 2
11 6
12 3
请教各位谢谢！可以从下拉列表选择年月动态显示这个统计列表！
4 个解决方案
#1
select day(submittime) '日期',sum(mobcount)'提交条数' from Jp_MobBook a join Jp_SMSInfo b on a.ID=b.BookID group by convert(varchar(10),submittime,120),day(submittime)
#2
-- try
select year(submittime) as y, month(submittime ) as m , day(submittime) as d, sum(isnull(m.mobcount,0)) as mobcount from Jp_SMSInfo s
left join Jp_MobBook m on m.bookid = s.id
group by year(submittime) , month(submittime ) , day(submittime)
#3
select datepart(day,submittime) 日期,datepart(month,submittime) 月,datepart(year,submittime) 月,sum(mobcount) 提交条数
from Jp_MobBook a,Jp_SMSInfo b
where a.bookid=b.id group by datepart(day,submittime),datepart(month,submittime),datepart(year,submittime)
from Jp_MobBook a,Jp_SMSInfo b
where a.bookid=b.id group by datepart(day,submittime),datepart(month,submittime),datepart(year,submittime)
#4
不行啊。。。
还会出来重复的天数
还会出来重复的天数
#1
select day(submittime) '日期',sum(mobcount)'提交条数' from Jp_MobBook a join Jp_SMSInfo b on a.ID=b.BookID group by convert(varchar(10),submittime,120),day(submittime)
#2
-- try
select year(submittime) as y, month(submittime ) as m , day(submittime) as d, sum(isnull(m.mobcount,0)) as mobcount from Jp_SMSInfo s
left join Jp_MobBook m on m.bookid = s.id
group by year(submittime) , month(submittime ) , day(submittime)
#3
select datepart(day,submittime) 日期,datepart(month,submittime) 月,datepart(year,submittime) 月,sum(mobcount) 提交条数
from Jp_MobBook a,Jp_SMSInfo b
where a.bookid=b.id group by datepart(day,submittime),datepart(month,submittime),datepart(year,submittime)
from Jp_MobBook a,Jp_SMSInfo b
where a.bookid=b.id group by datepart(day,submittime),datepart(month,submittime),datepart(year,submittime)
#4
不行啊。。。
还会出来重复的天数
还会出来重复的天数