SQL Server中Group查询无法实现解决办法

　　在前一段时间中遇到一个需求..统计某一种商品在某一天中的销售数量,当天没有销售的时候,数量显示0.

　　这个不能用一般的Group来实现.所以需要变通一下,跟一个有1-31的一个集合来Group.

　　有2种方案.

　　planA:

　　SELECT SUM(ISNULL(BidsTrade_Money, 0)) AS [MONEY], a.number AS [DAY]FROM MASTER..spt_values a LEFT JOIN DDPM_T_Comm_BidsTrade b ON a.type = 'p' AND month([BidsTrade_DateCreated])='5' AND a.number = DAY(b.[BidsTrade_DateCreated]) AND YEAR([BidsTrade_DateCreated]) = '2010' WHERE a.number BETWEEN 1 AND 31GROUP BY a.numberorder by DAY

　　使用MASTER..spt_values(产生一定范围的数字的数字，这里需要产生1-31的数字.)

　　

　　当然这个函数有其他的扩展应用请各位看官天马行空.~

　　实际应用,这里写了一个存储过程

　　SELECT a.day , isnull(BidsCombo_Price,0) as BidsCombo_Price, isnull( BidsTrade_Count,0) as BidsTrade_CountFROM ( SELECT year(Date) as year,month(Date) as month,day(Date) as [day] FROM dbo.CN80s_DDPM_FN_GETDATE(@beginTime,@endTime,NULL) ) a LEFT JOIN DDPM_V_BidsTradeRecount b ON a.day = b.day AND a.year=b.year AND a.month=b.month GO