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
Ïà¹ØÎĵµ£º
SQL Server ×èÖ¹Á˶Ô×é¼þ 'Ad Hoc Distributed Queries' µÄ STATEMENT'OpenRowset/OpenDatasource' µÄ·ÃÎÊ£¬ÒòΪ´Ë×é¼þÒÑ×÷Ϊ´Ë·þÎñÆ÷°²È«ÅäÖõÄÒ»²¿·Ö¶ø±»¹Ø±Õ¡£ÏµÍ³¹ÜÀíÔ±¿ÉÒÔͨ¹ýʹÓà sp_configure ÆôÓà 'Ad Hoc Distributed Queries'¡£ÓйØÆôÓà 'Ad Hoc Distributed Queries' µÄÏêϸÐÅÏ¢£¬Çë²ÎÔÄ SQL Server Áª»ú´ÔÊéÖÐ ......
ÔÚVSÖÐдSQLÓï¾äµÄʱºò£¬Ç§ÍòÍòǧҪСÐÄÔÙСÐÄ£¬±ÈÈç ˵ Êý¾ÝÀàÐ͵ÄÆ¥Å䣬 µ¥ÒýºÅ£¨Õâ¸öÄÜ°ÑÈËÃÔËÀ£©
where ×Ó¾äÖпÉǧÍò²»ÄÜÓпոñ£¨µ±²éѯÌõ¼þΪ×Ö·û´®µÄʱºòÄÜ°ÑÄãŪ·è£¬ÎÒŪÕâ¸öµÄʱºò¶¼·èÁ˼¸´ÎÁË£¬Ê²Ã´¶¼¶Ô¾ÍÊDz鲻³öÀ´£¬µ÷ÊÔÁËN±é²Å·¢ÏÖ¡££©²»ÐÐÁË£¬³Ô·¹È¥£¬ÔÙ²»³Ô¿´¼û»îÈ˶¼ÏëÒ§ÁË¡£ ......
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò£¨Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ£©
oracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒÖÁ×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±í£©½«×îÏȱ»´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´ ......
ÐÐÁÐתÖõÄSQLÓï¾ä
ÁíÒ»ÖÖÐÐÁÐתÖà -- ¶¯Ì¬ sql ½»²æ±í
===========================================================
×÷Õß: wxy0327(http://wxy0327.itpub.net)
·¢±íÓÚ: 2006.05.10 09:11
·ÖÀà:
Oracle
³ö´¦: http://wxy0327.itpub.net/post/16888/88075
-------------------------- ......