Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLServerÈÎÒâÁÐÖ®¼äµÄ¾ÛºÏ

SQLServerÈÎÒâÁÐÖ®¼äµÄ¾ÛºÏ ÊÕ²Ø
sqlµÄmaxÖ®ÀàµÄ¾ÛºÏº¯ÊýÖ»ÄÜÕë¶ÔͬһÁеÄnÐÐÔËË㣬Èç¹û¶ÔnÁÐÔËË㣬һ°ã¶¼ÓÃcase Óï¾äÀ´Åжϣ¬Èç¹ûÁÐÉÙ»¹±È½ÏÈÝÒ×д£¬ÁжàÁ˾ÍÂé·³ÁË¡£ÕâÀï½éÉÜÒ»¸öͨ¹ýxmlºÏ²¢Áв¢×ªÎªÐм¯ºóÖ±½ÓÓþۺϺ¯ÊýÇóÖµµÄ·½·¨£¬²âÊÔÓÃÀýºÍ´úÂëÈçÏÂ
--------------------------------------------------------------------------------
/*
²âÊÔÃû³Æ£ºÀûÓà XML ÇóÈÎÒâÁÐÖ®¼äµÄ¾ÛºÏ
²âÊÔ¹¦ÄÜ£º¶ÔÒ»ÕűíµÄÁÐÊý¾Ý×ö min ¡¢ max ¡¢ sum ºÍ avg ÔËËã
ÔËÐÐÔ­Àí£º×ֶκϲ¢Îª xml ºó×ö xquery ²éѯתΪÐм¯ºó¾ÛºÏ
×÷Õߣº jinjazz £¨½üÉí¼ô£©
*/
-- ½¨Á¢²âÊÔ»·¾³
declare @t table (
id smallint ,
a smallint , b smallint ,
c smallint , d smallint ,
e smallint , f smallint )
 
insert into @t
select 1, 1, 2, 3, 4, 6, 7 union all
select 2, 34, 45, 56, 54, 9, 6
 
-- ²âÊÔÓï¾ä
select   a.*, c.*
from @t a outer apply(
select doc=(
select * from @t as doc where id= a. id  for xml path ( '' ), type   )
) b
outer apply(
select
min ( r) as minValue,
max ( r) as maxValue,
sum ( r) as sumValue,
avg ( r) as avgValue
  from (
    select cast ( cast ( d. n. query( 'text()' ) as varchar ( max )) as int ) as r
       from doc. nodes( '/a,b,c,d,e,f' ) D( n)) tt
) c
     
/* ²âÊÔ½á¹û
 
id     a      b      c      d      e      f      minValue    maxValue    sumValue    avgValue
------ ------ ------ ------ ------ ------ ------ ----------- ----------- ----------- -----------
1      1      2      3      4      6      7      1       


Ïà¹ØÎĵµ£º

Ö»ÈÃÖ¸¶¨µÄ»úÆ÷Á¬½ÓSQLServer·þÎñÆ÷ ÊÕ²Ø

Ö»ÈÃÖ¸¶¨µÄ»úÆ÷Á¬½ÓSQLServer·þÎñÆ÷ ÊÕ²Ø
±³¾°
Ö»Ïëʹij¸öIPµÄ¼ÆËã»ú²ÅÄÜÁ¬½Óµ½SQL Server·þÎñÆ÷£¬²»ÔÊÐíÆäËû¿Í»§¶ËÁ¬½Ó¡£
½â¾ö·½·¨
¿ÉÒÔÖ±½ÓÔÚ·À»ðǽÖÐ×öÏÞÖÆ£¬Ö»ÔÊÐíÓëÖ¸¶¨µÄIPµØÖ·½¨Á¢1433µÄͨѶ¡£µ±È»£¬´Ó¸üΪ°²È«µÄ½Ç¶ÈÀ´¿¼ÂÇ£¬Ó¦¸Ã°Ñ1433¶Ë¿Ú¸Ä³ÉÆäËûµÄ¶Ë¿Ú¡£
ÆäËû½â¾ö·½·¨1£¨ÏÞ´ÓÖ¸¶¨IP½ÓÈëµÄ¿Í»§¶Ë£©
Èç¹ûʹ ......

18¾ä»°ÈëÃÅSQLServer XML


/*
sql xml ÈëÃÅ:
    --by jinjazz
    --http://blog.csdn.net/jinjazz
   
    1¡¢xml:        ÄÜÈÏÊ¶ÔªËØ¡¢ÊôÐÔºÍÖµ
   
    2¡¢xpath:    ѰַÓïÑÔ£¬ ......

SQLServerÖеÄScanfºÍPrintf ÊÕ²Ø

SQLServerÖÐÓÐÁ½¸öÀ©Õ¹´æ´¢¹ý³ÌʵÏÖScanfºÍPrintf¹¦ÄÜ£¬Ç¡µ±µÄʹÓÃËüÃÇ¿ÉÒÔÔÚÌáÈ¡ºÍÆ´½Ó×Ö·û´®Ê±´ó·ù¶È¼ò»¯SQL´úÂë¡£
1¡¢xp_sscanf£¬ÓÃËü¿ÉÒÔ·Ö½â¸ñʽÏà¶Ô¹Ì¶¨µÄ×Ö·û´®£¬Õâ¶ÔÓÚÑá¾ëʹÓÃÒ»¶ÑsubstringºÍcharindexµÄÅóÓÑÀ´Ëµ²»´í¡£±ÈÈçǰ¼¸ÌìµÄÒ»¸öÌû×ÓÖÐÌá³öµÄÈçºÎ·Ö½âipµØÖ·£¬Ïà¶Ô¼òÁ·ÇÒͨÓõĴúÂëÓ¦¸ÃÊÇÏÂÃæÕâÑù
------- ......

Ëø³õ̽(sqlserver 2005)

HOLDLOCK ½«¹²ÏíËø±£Áôµ½ÊÂÎñÍê³É£¬¶ø²»ÊÇÔÚÏàÓ¦µÄ±í¡¢ÐлòÊý¾ÝÒ³²»ÔÙÐèҪʱ¾ÍÁ¢¼´ÊÍ·ÅËø¡£HOLDLOCK µÈͬÓÚ SERIALIZABLE¡£
NOLOCK ²»Òª·¢³ö¹²ÏíËø£¬²¢ÇÒ²»ÒªÌṩÅÅËüËø¡£µ±´ËÑ¡ÏîÉúЧʱ£¬¿ÉÄÜ»á¶ÁȡδÌá½»µÄÊÂÎñ»òÒ»×éÔÚ¶ÁÈ¡ÖÐ¼ä»Ø¹öµÄÒ³Ãæ¡£ÓпÉÄÜ·¢ÉúÔà¶Á¡£½öÓ¦ÓÃÓÚ SELECT Óï¾ä¡£
 PAGLOCK ÔÚͨ³£Ê¹Óõ¥¸ö±íËøµ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ