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

Sql Server2005¶Ôt sqlµÄÔöÇ¿Ö®Cross Apply

Cross Applyʹ±í¿ÉÒԺͱíÖµº¯Êý½á¹û½øÐÐjoin£¬ÔÚÏÂÃæµÄʾÀýÖн¨ÁËÁ½¸ö±íºÍÒ»¸ö±íÖµº¯Êý£¬T_bµÄÁÐa_idsÖлá´æ·Åa±íµÄidÓÃ,·Ö¸îµÄ×Ö·û´®Á¬½Ó£»ÎÒÃÇͨ¹ýcross applyʹT_a£¬T_b±íͨ¹ýsplitIDs inner join Á¬½Ó¡£Ç뿴ʾÀý£ºGO
if object_id('T_a','U') is not null
drop table T_a
GO
CREATE TABLE T_a(    id int unique not null,
    name varchar(50),
)
GO
if object_id('T_b',N'U') is not null
drop table T_b
GO
create table T_b
(
    id int unique not null,
    name varchar(10),
    a_ids varchar(100) null --ÒªÔÚÕâÒ»ÁÐÖдæ·Åt_a±íµÄIDÐòÁÐ,ÕâÑù×öÁ¬µÚÒ»·¶Ê½¶¼Ã»ÓÐÂú×㣬µ«ÊÇÓÐʱºò¿¼ÂÇÐÔÄÜ»òÉè¼ÆÎÒÃÇ¿ÉÄÜ»áÏñÕâôÓÃ
)
GO
--³õʼ»¯Êý¾Ý
INSERT INTO T_a VALUES(1,'A-1')
INSERT INTO T_a VALUES(2,'A-2')
INSERT INTO T_a VALUES(3,'A-3')
INSERT INTO T_a VALUES(4,'A-4')
INSERT INTO T_a VALUES(5,'A-5')
INSERT INTO T_b VALUES(1,'B-1','1,2,4')
GO
--´´½¨Ò»¸ö±íÖµº¯Êý£¬ÓÃÀ´²ð·ÖÓöººÅ·Ö¸îµÄÊý×Ö´®£¬·µ»ØÖ»ÓÐÒ»ÁÐÊý×ֵıí
if object_id('splitIDs','TF') is not null
drop function splitIDs;
GO
create function splitIDs(
    @Ids nvarchar(1000)
)
returns @t_id TABLE (id bigint)
as
begin
    declare @i int,@j int,@l int,@v bigint;
    set @i = 0;
    set @j = 0;
    set @l = len(@Ids);
    while(@j < @l)
    begin
       set @j = charindex(',',@Ids,@i+1);
       if(@j = 0) set @j = @l+1;
       set @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as bigint);
       INSERT INTO @t_id VALUES(@v)
       set @i = @j;
    end
    return;
end
GO
--²âÊÔsplitIDsµÄÖ´ÐÐЧ¹û
select * from splitIDs('1,2,4,3')
select * from splitIDs('100')
select * from splitIDs(NULL)
GO
--ʹÓÃcross apply»ñµÃt_b±íÖÐÖ¸¶¨ÐжÔÓ¦µÄËùÓÐt_a±íÖеļǼ
select 


Ïà¹ØÎĵµ£º

ÎåÖÖÌá¸ß SQL ÐÔÄܵķ½·¨

ÎåÖÖÌá¸ß SQL ÐÔÄܵķ½·¨
ÓÐʱ£¬ ΪÁËÈÃÓ¦ÓóÌÐòÔËÐеøü¿ì£¬Ëù×öµÄÈ«²¿¹¤×÷¾ÍÊÇÔÚÕâÀï»òÄÇÀï×öһЩºÜСµ÷Õû¡£°¡£¬µ«¹Ø¼üÔÚÓÚÈ·¶¨ÈçºÎ½øÐе÷Õû£¡³ÙÔçÄú»áÓöµ½ÕâÖÖÇé¿ö£ºÓ¦ÓóÌÐòÖÐµÄ SQL ²éѯ²»ÄÜ°´ÕÕÄúÏëÒªµÄ·½Ê½½øÐÐÏìÓ¦¡£ËüҪô²»·µ»ØÊý¾Ý£¬ÒªÃ´ºÄ·ÑµÄʱ¼ä³¤µÃ³öÆæ¡£Èç¹ûËü½µµÍÁ˱¨¸æ»òÄúµÄÆóÒµÓ¦ÓóÌÐòµÄËٶȣ¬Óû§±ØÐ ......

sql ²éѯÂýµÄ48¸öÔ­Òò·ÖÎö

1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)
¡¡¡¡7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣠......

sql CHARINDEX

CHARINDEXº¯Êý·µ»Ø×Ö·û»òÕß×Ö·û´®ÔÚÁíÒ»¸ö×Ö·û´®ÖеÄÆðʼλÖá£CHARINDEXº¯Êýµ÷Ó÷½·¨ÈçÏ£º
       CHARINDEX ( expression1 , expression2 [ , start_location ] )
        Expression1ÊÇÒªµ½expression2ÖÐÑ°ÕÒµÄ×Ö·ûÖУ¬start_locationÊÇCHAR ......

Visual Basic 6.0 Óë SQL Server µÄÁ½ÖÖÁ¬½Ó

VBͨ¹ýADOÁ¬½ÓSQL ServerÊý¾Ý¿â¿ÉÒÔ·ÖΪÓÐÔ´Êý¾Ý¿âÁ¬½ÓºÍÎÞÔ´Êý¾Ý¿âÁ¬½Ó£¬Çø±ðËüÃÇ¿´ÊÇ·ñʹÓÃÁËDSN£¨Êý¾ÝÔ´Ãû³Æ£©À´¾ö¶¨¡£
1   ÎÞÔ´Êý¾Ý¿âÁ¬½Ó
Æäʵ£¬“ÎÞÔ´Êý¾Ý¿â”Á¬½ÓÒ²²¢²»ÊÇ˵²»ÐèÒªÊý¾ÝÔ´£¬Èç¹ûûÓÐÊý¾ÝÔ´ÔòÁ¬½ÓÊÇÖ½ÉÏ̸±ø£¬Ö»ÊDz»ÐèÒªÅäÖÃODBCÊý¾ÝÔ´£¬ÀûÓÃADO¾Í¿Éͨ¹ý´úÂë½øÐÐÁ¬½Ó¡£
1.1 ADO ......

MS SQL Server²éѯÓÅ»¯·½·¨

²éѯËÙ¶ÈÂýµÄÔ­ÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ 
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)  
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£ 
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£  
4¡¢ÄÚ´æ²»×ã 
5¡¢ÍøÂçËÙ¶ÈÂý 
6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäË ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ