表结构如下:
tblHistory(fID, fSongCode, fOrderTime)
说明: fID 是主键,自增字段.
fSongCode 是外键, 在该表会重复.
fOrderTime 记录该记录写入的时间..想得到的数据集是:
fSongCode, 周数据汇总, 月数据汇总 (所谓的汇总,只是把在一周内或是一月内的数据计数)我只会通过类似的语句,得到两个数据集..
fSongCode, fWeekTotal, 另一个: fSongCode, fMonthTotal
SQL语句为:
select fSongCode, count(*) as fWeekTotal from tblHistory
where fOrderTime > getdate() - 7 -- 统计一周内的数量 或者: getdate() - 30
group by fSongCode可是,我并不想先查询,得到两个数据集,然后再想办法来合并两个数据集.
另外,确切地说,按照上面的写法, 得到的是:"7天以内的汇总, 30 天内的汇总",并非"本周数据汇总","本月数据汇总"..
tblHistory(fID, fSongCode, fOrderTime)
说明: fID 是主键,自增字段.
fSongCode 是外键, 在该表会重复.
fOrderTime 记录该记录写入的时间..想得到的数据集是:
fSongCode, 周数据汇总, 月数据汇总 (所谓的汇总,只是把在一周内或是一月内的数据计数)我只会通过类似的语句,得到两个数据集..
fSongCode, fWeekTotal, 另一个: fSongCode, fMonthTotal
SQL语句为:
select fSongCode, count(*) as fWeekTotal from tblHistory
where fOrderTime > getdate() - 7 -- 统计一周内的数量 或者: getdate() - 30
group by fSongCode可是,我并不想先查询,得到两个数据集,然后再想办法来合并两个数据集.
另外,确切地说,按照上面的写法, 得到的是:"7天以内的汇总, 30 天内的汇总",并非"本周数据汇总","本月数据汇总"..
select fSongCode,
case when fOrderTime > getdate() - 7 then count(fOrderTime ) end as fWeekTotal from tblHistory group by fSongCode可是上面的语句,会报错,说 fOrderTime 没有在分组条件里..
这点我好理解,确实存在着问题.只是不知道: 怎样解决才能更好呢?
WHERE fOrderTime >=DATEADD(dd,-(DATEPART(WEEKDAY,GETDATE())-1),GETDATE())
AND fOrderTime <=DATEADD(dd,7-DATEPART(WEEKDAY,GETDATE()),GETDATE())
select * from tblHistory left join
(select fSongCode, count(1) as week from tblHistory where fOrderTime > getdate() - 7)
as tweek on tblHistory.fSongCode
left join
(select fSongCode, count(1) as mon from tblHistory where fOrderTime > getdate() - 30)
as tmon on tblHistory.fSongCode
then sum(1)
else sum(0) end as '周数据汇总',
case when datediff(month,fOrderTime ,getdate())=0
then sum(1)
else sum(0) end as '月数据汇总'
from tblHistory
group by fSongCode
不知道能不能达到你的要求.
select fSongCode ,sum(Sumweek) as '周数据汇总',sum(Summonth) as '月数据汇总'
from
(
select fSongCode, count(fSongCode) as Sumweek,0 as Summonth
from #tblHistory
where datediff(week,fOrderTime,getdate())=0
group by fSongCode
union all
select fSongCode,0 as Sumweek, count(fSongCode) as Summonth
from #tblHistory
where datediff(month,fOrderTime ,getdate())=0
group by fSongCode
) A
group by fSongCode
这个行吗?
呵呵。