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

SQL 2005ÖÐÓÃXML nodes()º¯ÊýÈ¡´úOPENXML

SQL Server 2005ΪXMLÌṩ±¾µØÖ§³Ö;ÏÖÔÚ¿ÉÒÔÔÚOPENXML½ÚµãÖÐʹÓÃÒ»¸ö´øÓÐnodes()º¯ÊýµÄXML×Ö¶ÎÀàÐͰÑÒ»¸öXMLÎļþת»¯ÎªÒ»¸öÐм¯¡£ÈÃÎÒÃÇÀ´¿´Ò»¸öʹÓÃOPENXMLµÄ¼òµ¥Àý×Ó£¬²¢ÏÔʾÈçºÎ°ÑËüת»¯ÎªÔÚSQL Server 2005ÖÐʹÓÃXML×Ö¶ÎÀàÐͺÍnodes()º¯Êý¡£
¡¡¡¡ÎªÁËʹÎÒÃǵÄÀý×Ó¼òµ¥»¯£¬ÎÒÃǽ«¼ÙÉèÎÒÃÇÐèÒªÖ´ÐÐijÖÖ»ùÓڲɹº¶©µ¥ÁбíµÄ¹ý³Ì£¬¶øÕâ¸ö²É¹º¶©µ¥½«ÔÚÒ»¸öXMLÎļþÖÐÖ¸¶¨¡£¸ÃXMLÎļþÀàËÆÓÚÕâÑù£º
¡¡¡¡ < polist>
¡¡¡¡< po>< ponumber>100< /ponumber>< podate>2008-09-10< /podate>< /po>
¡¡¡¡< po>< ponumber>101< /ponumber>< podate>2008-09-11< /podate>< /po>
¡¡¡¡< /polist>
¡¡¡¡ÎÒÃÇ¿ÉÒÔͨ¹ýÏÂÃæµÄOPENXML°Ñ XMLת»¯ÎªÒ»¸öÐм¯£º
¡¡¡¡ DECLARE @DocHandle int
¡¡¡¡DECLARE @XmlDocument nvarchar(1000)
¡¡¡¡SET @XmlDocument = N''< polist>
¡¡¡¡< po>< ponumber>100< /ponumber>< podate>2008-09-10< /podate>< /po>
¡¡¡¡< po>< ponumber>101< /ponumber>< podate>2008-09-11< /podate>< /po>
¡¡¡¡< /polist>''
¡¡¡¡EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
¡¡¡¡SELECT * from OPENXML (@DocHandle, ''/polist/po'',2)
¡¡¡¡WITH (ponumber nvarchar(10),
¡¡¡¡podate datetime)
¡¡¡¡EXEC sp_xml_removedocument @DocHandle
¡¡¡¡ÏÂÃæÊÇÖ´ÐÐÒÔÉÏ´úÂë¶ÎµÃµ½µÄÐм¯£º
¡¡¡¡
¡¡¡¡Í¼Ò»
¡¡¡¡ÕâÊÇ´ÓÉÏÃæ´úÂë¶ÎµÃµ½µÄÒªµã£º
¡¡¡¡Ê¹ÓÃOPENXMLµÄµÚÒ»²½ÊÇͨ¹ý·ÃÎʸô洢¹ý³Ìsp_xml_preparedocumentÀ´"×¼±¸"XMLÎļþ£¬¸Ã´æ´¢¹ý³Ì½«·µ»ØÒ»¸öÈ·ÈÏ×¼±¸ºÃµÄXMLµÄÕûÊý¡£
¡¡¡¡OPENXMLÊÇÒ»¸öÐм¯ÌṩÕߣ¬ÕâÒâζ×ÅÈç¹ûËüÊÇÒ»ÕÅ±í£¬ÄÇôÄãͬÑù¿ÉÒÔʹÓÃËü¡£±¾ÖÊÉÏËüÊÇÒ»¸ö´øÓвÎÊýµÄº¯Êý£¬ÕâЩ²ÎÊý½«È·¶¨¸Ã´ÓXMLÎļþÖÐÌáȡʲô¡£
¡¡¡¡Ê¹ÓÃOPENXMLµÄ×îºó²½ÖèÊÇͨ¹ý·ÃÎÊ´æ´¢¹ý³Ìsp_xml_removedocumentÀ´"ÊÍ·Å"×¼±¸ºÃµÄXMLÎļþ¡£
ÉÏÃæµÄxml´æ´¢¹ý³ÌʹÓù²Í¬µÄMSXML½âÎö×é¼þ¡£
¡¡¡¡sp_xml_preparedocument´æ´¢¹ý³Ì°Ñ×¼±¸ºÃµÄXMLÎļþ´æ´¢ÔÚSQL ServerµÄÄÚ²¿¸ßËÙ»º´æÖС£·ÃÎÊsp_xml_removedocument´æ´¢¹ý³ÌÊǺÜÓбØÒªµÄ£¬ÕâÊÇΪÁ˰Ñ×¼±¸ºÃµÄXMLÎļþ´Ó¸ßËÙ»º´æÖÐÏû³ý¡£¸ù¾ÝSQL Server 2005Áª»ú°ïÖú£¬Äܹ»ÓÃÓÚSQL ServerµÄ°Ë·ÖÖ®Ò»×ÜÄÚ´æ¿ÉÒÔÓÃÓÚMSX


Ïà¹ØÎĵµ£º

¾«ÃîSQLÓï¾äѧϰ

SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname ......

sqlµÄ¼¸¸öÅÅÐòº¯Êý

1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......

SQL ServerºÍOracleµÄ³£Óú¯Êý¶Ô±È

---------Êýѧº¯Êý
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast(-1.002 as int) v ......

SQLÓï¾ä ÁªºÏ²éѯ»¹ÊÇÔÚ²éѯ½á¹ûÖÐǶÌ×

TABLE MASTER   ×Ö¶Î ID DETAIL.....
TABLE BIZ   ×Ö¶Î SYS_ID  CODE_ID .......
²éѯʱÐèÒªµÄÊÇIDµÄÃèÊö
1,SELECT A.SYS_ID,A.CODE_ID,B.DETAIL,C.DETAIL...... from BIZ A,MASTER B,MASTER C WHERE A.SYS_ID=B.ID AND A.CODE_ID=C.ID
2,SELECT SYS_ID,(SELECT DETAIL from MASTER ......

SQL×Ö·û´®´¦Àíº¯Êý´óÈ«

selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔ­ÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ