SQL °´Ô²éѯ

ÏîÄ¿ÖÐÐèÒª¸ù¾Ý¿Î¼þÃû³Æ°´ÔÂͳ¼Æ³ö·ÃÎʵÄÇé¿ö£¬µÚÒ»´ÎÎÒ²ÉÓÃÁË×îÍÁµÄÒ»ÖÖ°ì·¨£¬Ê¹ÓÃÑ­»·£¬¸øsql´«µÝÄêÔÂÁ½¸ö²ÎÊý£¬
for(var y=2009;y<=2010;y++){
    for(var m=1;m<=12;m++){
          // todo : SQL ²éѯ
    }
}
ÕâÑù£¬Í³¼Æ2009Äêµ½2010ÄêµÄÔ±¨±í£¬¾ÍÐèҪѭ»·2*12 ´Î£¬Èç¹ûÊý¾Ý¿âÖÐijÔ²¢Î´ÓÐÊý¾Ý£¬ÄÇô¾ÍµÈÓÚÊÇ°×°×Õ¼Óôø¿íÇëÇó·þÎñÆ÷ÁË¡£
¾­¹ýlinchunµÄµã²¦£¬½áºÏ±ù¸ç֮ǰµÄ²éѯ£¬Ê¹ÓÃConvert ºÍDatepart ¼´¿ÉʵÏÖÕâÖÖ²éѯ£¬È»ºóÔÙÔÚǰ̨ͨ¹ýJavaScript´¦ÀíÊý¾Ý¡£
SELECT a.[ID]
,a.[Name]
,fna(a.[ID], CONVERT([datetime],CONVERT([varchar](8),b.[Timestamp],(102))+'01',0) ) as UserCount ,(CONVERT([datetime],CONVERT([varchar](8),b.[Timestamp],(102))+'01',0)) as ReportDate
from [dbo].[lcms_CourseWare] a
inner join [dbo].[lcms_SessionTime] b
on a.[ID] = b.[CourseWareID]
where [State]=1
and [DelFlag]=0
group by (CONVERT([datetime],CONVERT([varchar](8),b.[Timestamp],(102))+'01',0))
,a.[ID],a.[Name]
ÔÚfunction ÖУ¬½ÓÊÕʱ¼ä²ÎÊý£º
CREATE FUNCTION fna(
@CourseWareID varchar(32)
, @ReportDate datetime
)
RETURNS bigint
AS
BEGIN
RETURN (
SELECT COUNT(distinct [UserID])
from [dbo].[lcms_SessionTime]
WHERE CourseWareID = @CourseWareID
and year([Timestamp])=DATEPART(YEAR,@ReportDate)
and month([Timestamp])=DATEPART(MONTH,@ReportDate)
)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO