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 UNION ²Ù×÷·û
UNION ²Ù×÷·ûÓÃÓںϲ¢Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û¼¯¡£
Çë×¢Ò⣬UNION ÄÚ²¿µÄ SELECT Óï¾ä±ØÐëÓµÓÐÏàͬÊýÁ¿µÄÁС£ÁÐÒ²±ØÐëÓµÓÐÏàËÆµÄÊý¾ÝÀàÐÍ¡£Í¬Ê±£¬Ã¿Ìõ SELECT Óï¾äÖеÄÁеÄ˳Ðò±ØÐëÏàͬ¡£
SQL UNION Óï·¨
SELECT column_name(s) from table_name1
UNION
SELECT column_name(s) from table_na ......
select * from tt t inner loop join ss s with(nolock) on s.c=t.c
ʹÓà nested join
select * from tt t inner merge join ss s with(nolock) on s.c=t.c
ʹÓà merge join
select * from tt t inner hash join ss s with(nolock) on s.c=t.c
ʹÓà hash jion
&n ......
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 ......
Ö´ÐÐ Êý¾Ý¿â²éѯʱ£¬ÓÐÍêÕû²éѯºÍÄ£ºý²éѯ֮·Ö¡£
Ò»°ãÄ£ºýÓï¾äÈçÏ£º
SELECT ×Ö¶Î from ±í WHERE ij×Ö¶Î Like Ìõ¼þ
ÆäÖйØÓÚÌõ¼þ£¬SQLÌṩÁËËÄÖÖÆ¥Åäģʽ£º
1£¬%£º±íʾÈÎÒâ0¸ö»ò¶à¸ö×Ö·û¡£¿ÉÆ¥ÅäÈÎÒâÀàÐͺͳ¤¶ÈµÄ×Ö·û£¬ÓÐЩÇé¿öÏÂÈôÊÇÖÐÎÄ£¬ÇëÔËÓÃÁ½¸ö°Ù·ÖºÅ£¨%%£©±íʾ¡£
±ÈÈç SELECT * from [user] WHERE u_na ......
SQL Server 2008ÖеıíÖµÐͲÎÊý
×÷ÕߣºAl Tenhundfeld ÒëÕß Õź£Áú¡¡
±íÖµÐͲÎÊý£¨Table-valued parameters£©ÊÇSQL Server 2008ÖÐÒýÈëµÄÒ»ÖÖÐÂÌØÐÔ£¬ËüÌṩÁËÒ»ÖÖÄÚÖõķ½Ê½£¬Èÿͻ§¶ËÓ¦ÓÿÉÒÔֻͨ¹ýµ¥¶ÀµÄÒ»Ìõ²Î»¯ÊýSQLÓï¾ä£¬¾Í¿ÉÒÔÏòSQL Server·¢ËͶàÐÐÊý¾Ý¡£
±íÖ ......