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
Ïà¹ØÎĵµ£º
--»ñµÃµ±Ç°ËùÓÐÇý¶¯Æ÷
exec master.dbo.xp_availablemedia
--»ñµÃ×ÓĿ¼Áбí
exec master.dbo.xp_subdirs 'c:\'
--»ñµÃËùÓÐ×ÓĿ¼µÄĿ¼Ê÷½á¹¹
exec master.dbo.xp_dirtree 'c:\'
--¶©ÔÄÎļþɾ³ý
declare @tempTable table (Cont varchar(50))
declare @strTemp varchar(50)
declare @dirPath varchar(50)
declar ......
Èçͼ1¡¢2£¬id=1µÄÊý¾ÝÊÇNULL£¬ÆäËûµÄΪ·ÇNULLµÄÊý¾Ý¡£
Ò»°ãÇé¿öÏ£¬»áÓÃÁ½ÖÖ·½·¨£¡
·½·¨1.t-sql£ºinsert into E values(1,'NULL')£¬²åÈëºó£¬ÔÚ´ò¿ª±íµÄÇé¿öÏ¿´µ½µÄ
ÊÇ'NULL'£¨ÎÒÏëÊÇΪÁËÇø·ÖNULL£¬²Å¼ÓµÄÒýºÅ£©£¬µ«ÊDzéѯµÄʱºò²»Ó°Ï죬ÏÔʾµÄÊÇNULL£¬
Èçͼ1¡¢2£¬idΪ6µÄÊý¾Ý¡£
Èç¹ûÒª²åÈë´øµ¥ÒýºÅµÄ'NULL'£¬insert i ......
ºÜÉÙÓÃjoin£¬Õâ´Îѧѧ£¬²¢±¸ÍüÁ½ÆªÎÄÕ£¡
ת×Ô£ºhttp://hcx-2008.javaeye.com/blog/285661
Á¬½Ó²éѯ
ͨ¹ýÁ¬½ÓÔËËã·û¿ÉÒÔʵÏÖ¶à¸ö±í²éѯ¡£Á¬½ÓÊǹØÏµÊý¾Ý¿âÄ£Ð͵ÄÖ÷ÒªÌØµã£¬Ò²ÊÇËüÇø±ðÓÚÆäËüÀàÐÍÊý¾Ý¿â¹ÜÀíϵͳµÄÒ»¸ö±êÖ¾¡£
ÔÚ¹ØÏµÊý¾Ý¿â¹ÜÀíϵͳÖУ¬±í½¨Á¢Ê±¸÷Êý¾ÝÖ®¼äµÄ¹ØÏµ²»±ØÈ·¶¨£¬³£°ÑÒ»¸öʵÌåµÄËùÓÐÐÅÏ¢´æ·ÅÔÚÒ ......
Access µÄ SQL ÖУ¬¼ÆËãÏàÓ¦±àºÅµÄ¼Ç¼֮¼ä ¼Ó ¼õ µÄ²éѯÃüÁî
ÀýÈç ¼ÆËãIDΪ 6 µÄ ºÏ¼Æ ¼õÈ¥ ID Ϊ 2¡¢3¡¢4 ÒÔ¼°¼ÓÉÏ ID Ϊ 5 µÄºÏ¼Æ
SELECT SUM(R) AS ½á¹û
from
(
SELECT ºÏ¼Æ AS R from T1 WHERE (ID = 6) UNION
SELECT -ºÏ¼Æ AS R from T1 WHERE (ID = 2) UNION
SELECT -ºÏ¼Æ AS R f ......