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

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


Ïà¹ØÎĵµ£º

SQL »ñȡϵͳÎļþ

--»ñµÃµ±Ç°ËùÓÐÇý¶¯Æ÷
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 ......

ÔÚsql serverÖÐÊäÈë'NULL'×Ö·û´®µÄÓÐȤ·¢ÏÖ£¡

Èçͼ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 ......

SQL ¶à±íÁªºÏ²éѯ

ºÜÉÙÓÃjoin£¬Õâ´Îѧѧ£¬²¢±¸ÍüÁ½ÆªÎÄÕ£¡
ת×Ô£ºhttp://hcx-2008.javaeye.com/blog/285661
Á¬½Ó²éѯ
ͨ¹ýÁ¬½ÓÔËËã·û¿ÉÒÔʵÏÖ¶à¸ö±í²éѯ¡£Á¬½ÓÊǹØÏµÊý¾Ý¿âÄ£Ð͵ÄÖ÷ÒªÌØµã£¬Ò²ÊÇËüÇø±ðÓÚÆäËüÀàÐÍÊý¾Ý¿â¹ÜÀíϵͳµÄÒ»¸ö±êÖ¾¡£
ÔÚ¹ØÏµÊý¾Ý¿â¹ÜÀíϵͳÖУ¬±í½¨Á¢Ê±¸÷Êý¾ÝÖ®¼äµÄ¹ØÏµ²»±ØÈ·¶¨£¬³£°ÑÒ»¸öʵÌåµÄËùÓÐÐÅÏ¢´æ·ÅÔÚÒ ......

Access µÄ SQL µÄ¼Ç¼¼äµÄ¼Ó¼õ¼ÆËã

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 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ