表结构如下：
能过查询语句
- SQL code
select RIGHT( convert(varchar(10),updateTime,120),2) as 'dd', sum(voteNum) as 'vnum',count(distinct uid) as 'voteusernum' ,convert(nvarchar(10),updateTime,120) as 'udtime' from r_vipvote_uid where year(updateTime)=year('2012-10-01') and month(updateTime)=month('2012-10-01') group by convert(nvarchar(10),updateTime,120),RIGHT( convert(varchar(10),updateTime,120),2)得到如下结果
这只显示了1号到7号的数据。我想要把所有日期都查出来如1到31号（发果还没到31号的也查出来），如下
dd vnum voteusernum udtime
1 100 20 2012-10-01
2 100 20 2012-10-02
3 100 20 2012-10-03
4 100 20 2012-10-04
5 100 20 2012-10-05
...
31 0 0 null
没有数据的用0和null
请各路高手帮个忙，那SQL是什么写的！
跪谢！
------解决方案--------------------
- SQL code
SELECT RIGHT(a.dt,2) AS dt,vnum,voteusernumFROM (SELECT CONVERT(NVARCHAR(10),DATEADD(dd,number,DATEADD(mm,DATEDIFF(mm,0,'2012-10-01'),0)),120) AS dtFROM MASTER..spt_values AS sWHERE type='p' AND number BETWEEN 0 AND DATEDIFF(dd,'2012-10-01',DATEADD(mm,1,'2012-10-01')-1)) aleft JOIN (SELECT SUM(voteNum) AS 'vnum' , COUNT(DISTINCT uid) AS 'voteusernum' , CONVERT(NVARCHAR(10) , updateTime , 120) AS 'udtime'FROM r_vipvote_uidWHERE YEAR(updateTime) = YEAR('2012-10-01') AND MONTH(updateTime) = MONTH('2012-10-01')GROUP BY CONVERT(NVARCHAR(10) , updateTime , 120)) bON a.dt=b.udtime
------解决方案--------------------
- SQL code
create table #r_vipvote_uid (id int,uid int,writingid int,updateTime datetime,ip varchar(30),votenum int,fuid int,votecount int)select * from #r_vipvote_uidinsert into #r_vipvote_uidselect 1,7,4932,'2011-02-03 00:24:16.597','113.109.221.49',1,7,0 union allselect 2,1237,3492,'2011-02-04 00:26:05.597','113.129.221.49',1,1237,0 union allselect 3,237,3289,'2011-09-05 00:27:12.597','113.139.221.49',1,237,0 union allselect 4,347,2292,'2011-09-08 00:28:18.597','113.149.221.49',1,347,0 union allselect 5,37,3392,'2011-09-10 00:29:16.597','113.159.221.49',1,37,0 union allselect 6,987,1942,'2011-09-12 00:30:16.597','113.169.221.49',1,987,0 union allselect 7,4567,2954,'2011-09-25 00:31:16.597','113.189.221.49',1,4567,0 union allselect 8,3457,3921,'2011-09-26 00:31:30.597','113.199.221.49',1,3457,0 union allselect 9,7567,2492,'2011-09-27 00:45:28.597','113.129.221.149',1,7567,0 ;with cte_dateas( select distinct cast(left(convert(nvarchar(10),updateTime,120),8)+'01'as datetime) as year_month from #r_vipvote_uid -- where year(updateTime)=year('2012-10-01') and month(updateTime)=month('2012-10-01')),cte_alldateas( select convert(varchar(30),dateadd(day,b.number-1,year_month),120) as date from cte_date a inner join master.dbo.spt_values b on b.type='p' and b.number between 1 and datediff(day,year_month,dateadd(month,1,year_month)))select RIGHT( convert(varchar(10),a.date,120),2) as 'dd' ,sum(voteNum) as 'vnum' ,count(distinct uid) as 'voteusernum' ,convert(nvarchar(10),a.date,120) as 'udtime' from cte_alldate a left join #r_vipvote_uid b on convert(nvarchar(10),a.date,120)=convert(nvarchar(10),b.updateTime,120)group by convert(nvarchar(10),a.date,120),RIGHT( convert(varchar(10),a.date,120),2)order by udtime
------解决方案--------------------
- SQL code
year(updateTime)=year('2012-10-01') and month(updateTime)=month('2012-10-01')
------解决方案--------------------