SQL¾µä×éºÏ²éѯ
´Ó²©¿ÍÔ°Öп´µ½Ò»ÆªÎÄÕ£¬½éÉÜ´óÈí¼þ¹«Ë¾ÃæÊÔʱ³£³£»á³öµÄÁ½µÀSQLÌ⣨¼û¸½Â¼£©¡£
ÎÒ¾õµÃÊÜÒæºÜ¶à£¬ÔÚ´Ë֮ǰ£¬ÎÒÒ»Ö±¾õµÃ£¬SQL2008ËƺõÌṩÁËÕâ·½ÃæµÄÖ§³Ö£¬µ«¸üµÍµÄ°æ±¾£¬°üÀ¨2005£¬·ÇÓαê×ö²»³öÀ´£¨Ë®Æ½¹»²Ë£©¡£×ܽáÐĵÃÈçÏ£º
1¡¢ Ç¿´óµÄgroup by
1 select stdname,
2 isnull(sum( case stdsubject when ' »¯Ñ§ ' then Result end), 0 ) [»¯Ñ§],
3 isnull(sum( case stdsubject when ' Êýѧ ' then Result end), 0 ) [Êýѧ],
4 isnull(sum( case stdsubject when ' ÎïÀí ' then Result end), 0 ) [ÎïÀí],
5 isnull(sum( case stdsubject when ' ÓïÎÄ ' then Result end), 0 ) [ÓïÎÄ]
6 from #student
7 group by stdname
ÔÚÕâÀgroup byÓësum + case½áºÏ£¬¿ÉÒÔ½«±í1ÖеļǼ£¨ÐУ©±ä³É±í2µÄ×ֶΣ¨ÁУ©¡£SumÀïÃæÈç¹ûûÓÐcase£¬ÄÇô³öÀ´µÄÖµ£¬Ö»ÄÜÊÇÈ«²¿¿ÆÄ¿µÄ×ܺͣ¬ÓÃÁËcaseÒÔºó£¬¾Í ÊÇij¿ÆµÄ³É¼¨£»È»ºóÕâÀïÓÃÁ˺ü¸¸ösum£¬Ã¿¸ö¿ÆÄ¿Ò»¸ösum£¬ÓÚÊDZí1Öб¾À´Ä³ÈËij¿ÆÕ¼Ò»Ìõ¼Ç¼µÄ“ÐД¾Í±ä³ÉÁ˱í2ÀïijÈËÒ»Ìõ¼Ç¼£¬Ã¿¿Æ×öÒ»¸ö×ֶΠÁË¡£
ÕâÖÖÐÄ˼ÇÉÃîºÍ¶ÔÓï·¨µÄÊìÁ·ÔËÓÃÈÃÈË»÷½ÚÔÞ̾¡£
2¡¢ ÀûÓÃselect from (select from)µÄģʽÉú³ÉSQLÓï¾ä
1 declare @sql varchar( 4000 )
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
ΪÁË×Ô¶¯Ð´ÉÏËùÓеĿÆÄ¿£¬ÕâÀïÏȽ«¿ÆÄ¿ÐÅÏ¢ÌáÁ¶³öÀ´£º
4 from (select distinct stdsubject from #student) as &
Ïà¹ØÎĵµ£º
SQL Server DATEDIFF() º¯Êý
¶¨ÒåºÍÓ÷¨
DATEDIFF() º¯Êý·µ»ØÁ½¸öÈÕÆÚÖ®¼äµÄÌìÊý¡£
Óï·¨
DATEDIFF(datepart,startdate,enddate)
startdate ºÍ enddate ²ÎÊýÊǺϷ¨µÄÈÕÆÚ±í´ïʽ¡£
datepart ²ÎÊý¿ÉÒÔÊÇÏÂÁеÄÖµ£º
datepartËõд
Äê
yy, yyyy
¼¾¶È
qq, q
ÔÂ
mm, m
ÄêÖеÄÈÕ
dy, y
ÈÕ
dd, d
ÖÜ
wk, ww
ÐÇÆÚ
......
SQL ServerµÄһЩϵͳ±äÁ¿
sp_configure 'min server memory' --·þÎñÆ÷×îСÄÚ´ægosp_configure 'max server memory' --·þÎñÆ÷×î´óÄÚ´æ'gosp_configure 'index create memory'--´´½¨Ë÷ÒýÕ¼ÓõÄÄÚ´ægo--sp_configure 'min memory per query'--ÿ´Î²éѯռÓõÄ×îСÄÚ´æ
--»ñÈ¡I/O¹¤×÷Çé¿öselect -- @@id_bus ......
ÎÊÌâ±³¾°£º
¡¡¡¡ÈÕ³£¶ÔSql Server 2005¹ØϵÊý¾Ý¿â½øÐвÙ×÷ʱ£¬ÓÐʱ¶ÔÊý¾Ý¿â£¨È磺SharepointÍøÕ¾ÅäÖÃÊý¾Ý¿âÃû
Sharepoint_Config£©½øÐÐЩ²»Õý³£²Ù×÷ÈçÊý¾Ý¿âÔÚ¶Áдʱ¶øÎÞ¹ÊÍ£Ö¹Êý¾Ý¿â£¬´Ó¶øµ¼ÖÂSql Server 2005Êý¾Ý¿â²»Õý³£
Öжϣ¬µ±Ôٴδò¿ªÊý¾Ý¿âʱ»á·¢ÏÖijЩÊý¾Ý¿â»á±»±ê¼ÇΪ“¿ÉÒÉ”£¨suspect)£¬¼´ÔÚÊý¾ ......
SQL×¢ÈëÊÇ×î³£¼ûµÄ¹¥»÷·½Ê½Ö®Ò»,Ëü²»ÊÇÀûÓòÙ×÷ϵͳ»òÆäËüϵͳµÄ©¶´À´ÊµÏÖ¹¥»÷µÄ,¶øÊdzÌÐòÔ±ÒòΪûÓÐ×öºÃÅжÏ,±»²»·¨
Óû§×êÁËSQLµÄ¿Õ×Ó,ÏÂÃæÎÒÃÇÏÈÀ´¿´ÏÂʲôÊÇSQL×¢Èë:
±ÈÈçÔÚÒ»¸öµÇ½½çÃæ,ÒªÇóÓû§ÊäÈëÓû§ÃûºÍÃÜÂë:
& ......
SQL Server 2008ÖеıíÖµÐͲÎÊý
×÷ÕߣºAl Tenhundfeld ÒëÕß Õź£Áú¡¡
±íÖµÐͲÎÊý£¨Table-valued parameters£©ÊÇSQL Server 2008ÖÐÒýÈëµÄÒ»ÖÖÐÂÌØÐÔ£¬ËüÌṩÁËÒ»ÖÖÄÚÖõķ½Ê½£¬Èÿͻ§¶ËÓ¦ÓÿÉÒÔֻͨ¹ýµ¥¶ÀµÄÒ»Ìõ²Î»¯ÊýSQLÓï¾ä£¬¾Í¿ÉÒÔÏòSQL Server·¢ËͶàÐÐÊý¾Ý¡£
±íÖ ......