SQL·Ö¸î¶ººÅµÄº¯ÊýºÍÓ÷¨
ÔÚ³ÌÐòÖÐÎÒÃÇ¿ÉÄܾ³£»áÓöµ½ÕâÖÖÇé¿ö£¬±ÈÈçҪȡһ¸ölistboxÀïÃæµÄÑ¡ÔñÏµÃµ½µÄ½á¹û¿ÉÄÜÊÇstring ID="id1,id2,id3,id4",È»ºóÎÒÃÇÒª°ÑÕâЩID²åÈëµ½Êý¾Ý¿âÖУ¬Í¬Ê±Ã¿¸öid¶ÔÓ¦µÄÊÇÒª²åÈëÒ»Ìõ¼Ç¼¡£ÊµÏֵķ½·¨Óкܶ࣬µ«ÊÇÈç¹ûÎÒÃÇͨ¹ýÏÂÃæÕâ¸öº¯Êý£¨RecurrentSplit£©¾ÍÄܼòµ¥µÄ´ïµ½ÉÏÊöЧ¹û¡£RecurrentSplitµÄʹÓÃÒ²·Ç³£¼òµ¥¡£
ÀýÈ磺
select row_number()over(order by indexno desc) as seq ,* from recurrentsplit('1,2,3,4,5,',',',0,0)
ÕâÑùÎÒÃǾͻá·Ö³É5Ìõ¼Ç¼£¬ÒòΪÎÒµÄÒªÇóÊÇÒ»¸ölistboxÖÐitem[i]ÆäÖÐ×îСµÄ˵Ã÷ËûµÄÅÅÐòÔÚ×îÇ°Í·£¬Í¬Ê±ÅÅÐòÊǵ«SEQµÄ½µÐòÅÅÁС£ËùÒÔÔÚÕâ¸ö²éѯÖÐÎÒÓÃrow_number()over(order by indexno desc) as seqµÃ³öÁËËûµÄ˳ÐòºÅ
ÈçÏÂ
seq indexno SplitName
1 4 5
2 3 4
3 2 3
4 1 2
5 0 1
È»ºó¾ÍÊǶÔÕâ¸ö½á¹û½øÐвÙ×÷¿©
Èç
--ÉèÖÃÑ¡ÔñÉÌƷΪÍƼö²¢°´´«ÈëµÄ½µÐò½«ÉÌÆ·ÍƼöÅÅÐò
update zp_auction_mst
set ishot=1,hotseq = b.seq
from zp_auction_mst a,
(select row_number()over(order by indexno desc) as seq ,* from recurrentsplit(@AuctionID,',',0,0)) as b
where a.auctionid= b.splitName
¸ã¶¨£¬¾ÍÊÇÕâô¼òµ¥ÁË¡£¡£¡£¡£
CREATE FUNCTION [dbo].[RecurrentSplit]
(
@nvStr nvarchar(2000) --ÐèÒª·Ö¸î×Ö·û´®
,@vSeparte varchar(50) --·Ö¸î×Ö·û´®
,@iIsHaveSeparte int --ÊÇ·ñÏÔʾ×Ö·û´®
,@iIsBefore int --ÊÇ·ñÊǺóÃæµÄ·Ö¸î·û£¨·Ö¸î×Ö·û·Ö¸îµÄ˳Ðò£©
)
RETURNS @Split table
(
Ind
Ïà¹ØÎĵµ£º
declare @XML XML
SET @XML='<root>
<OLDVALUE>
<H_Action id="1130">030</H_Action>
<D_Action>030</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber> ......
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_convert]') and xtype in (N'FN', N'IF', N'TF'))&n ......
ÊÊÓû·¾³
²Ù×÷ϵͳ£ºwindows 2003
Êý¾Ý¿â£ºsql server 2000/sql server 2005
ʹÓÃÁ´½Ó·þÎñÆ÷½øÐÐÔ¶³ÌÊý¾Ý¿â·ÃÎʵÄÇé¿ö
Ò»¡¢ ÎÊÌâÏÖÏó
ÔÚÖ´Ðзֲ¼Ê½ÊÂÎñʱ£¬ÔÚsql server 2005ÏÂÊÕµ½ÈçÏ´íÎó£º
ÏûÏ¢ 7391£¬¼¶±ð 16£¬×´Ì¬ 2£¬¹ý³Ì xxxxx£¬µÚ 16 ÐÐ
ÎÞ·¨Ö´ÐиòÙ×÷£¬ÒòΪÁ´½Ó·þÎñÆ÷ "xxxxx" µÄ OLE DB ·ÃÎÊ½Ó¿Ú " ......
ºÜÉÙÓÃjoin£¬Õâ´Îѧѧ£¬²¢±¸ÍüÁ½ÆªÎÄÕ£¡
ת×Ô£ºhttp://hcx-2008.javaeye.com/blog/285661
Á¬½Ó²éѯ
ͨ¹ýÁ¬½ÓÔËËã·û¿ÉÒÔʵÏÖ¶à¸ö±í²éѯ¡£Á¬½ÓÊǹØϵÊý¾Ý¿âÄ£Ð͵ÄÖ÷ÒªÌص㣬ҲÊÇËüÇø±ðÓÚÆäËüÀàÐÍÊý¾Ý¿â¹ÜÀíϵͳµÄÒ»¸ö±êÖ¾¡£
ÔÚ¹ØϵÊý¾Ý¿â¹ÜÀíϵͳÖУ¬±í½¨Á¢Ê±¸÷Êý¾ÝÖ®¼äµÄ¹Øϵ²»±ØÈ·¶¨£¬³£°ÑÒ»¸öʵÌåµÄËùÓÐÐÅÏ¢´æ·ÅÔÚÒ ......
1.
SQL> desc gjh_a05 ;
Name Type Nullable Default Comments
----- ------------- -------- ------- --------
A0500 VARCHAR2(2) Y &nbs ......