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 
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
SQL JOIN
SQL join ÓÃÓÚ¸ù¾ÝÁ½¸ö»ò¶à¸ö±íÖеÄÁÐÖ®¼äµÄ¹Øϵ£¬´ÓÕâЩ±íÖвéѯÊý¾Ý¡£
Join ºÍ Key
ÓÐʱΪÁ˵õ½ÍêÕûµÄ½á¹û£¬ÎÒÃÇÐèÒª´ÓÁ½¸ö»ò¸ü¶àµÄ±íÖлñÈ¡½á¹û¡£ÎÒÃǾÍÐèÒªÖ´ÐÐ join¡£
Êý¾Ý¿âÖеıí¿Éͨ¹ý¼ü½«±Ë´ËÁªÏµÆðÀ´¡£Ö÷¼ü£¨Primary Key£©ÊÇÒ»¸öÁУ¬ÔÚÕâ¸öÁÐÖеÄÿһÐеÄÖµ¶¼ÊÇΨһµÄ¡£ÔÚ±íÖУ¬Ã¿¸öÖ÷¼üµÄ ......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£Õ ......
Ò»¡¢NATURAL JOIN£¨×ÔÈ»Á¬½Ó£©
Á½Õűíͨ¹ýNATURAL JOINÁ¬½ÓµÄʱºò£¬Ï൱ÓÚÓиöÒþº¬µÄWHERE×Ӿ䣬¶ÔÁ½ÕűíÖÐͬÃûµÄ¶ÔÓ¦ÁÐÏà±È½Ï¿´ÊÇ·ñÏàµÈ¡£
¶þ¡¢CROSS JOIN£¨´´½¨µÑ¿¨¶û»ý£©
¶ÔÁ½Õűíͨ¹ý½»²æÁªºÏ²úÉúµÚÈýÕÅ·µ»Ø½á¹û¼¯µÄ±í¡£Ï൱ÓÚÆÕͨµÄÁ¬½Ó¡£
Èý¡¢INNER JOIN£ ......