SQL Server 2005 T SQL cross Apply Óëouter apply
SQL Server 2005 T-SQL Apply
͸¹ýÖ´Ðмƻ®¿ÉÒÔ¿´³ö£¬cross applyÀàËƲ»´øwhereÌõ¼þµÄÁ¬½Ó¼´cross join £¨½»²æÁ¬½Ó¼´µÑ¿¨¶û»ý£º·µ»ØÐÐÊýΪ£ºÇ°±í·ûºÏÌõ¼þµÄÐгËÉϺó±í·ûºÏÌõ¼þµÄÐУ© ¡£ÐÎʽÉÏ»áÁé»îЩ.
ʹÓà APPLY ÔËËã·û¿ÉÒÔΪʵÏÖ²éѯ²Ù×÷µÄÍⲿ±í±í´ïʽ·µ»ØµÄÿ¸öÐе÷ÓñíÖµº¯Êý¡£±íÖµº¯Êý×÷ΪÓÒÊäÈ룬Íⲿ±í±í´ïʽ×÷Ϊ×óÊäÈë
¡£Í¨¹ý¶ÔÓÒÊäÈëÇóÖµÀ´»ñµÃ×óÊäÈëÿһÐеļÆËã½á¹û£¬Éú³ÉµÄÐб»×éºÏÆðÀ´×÷Ϊ×îÖÕÊä³ö¡£APPLY ÔËËã·ûÉú³ÉµÄÁеÄÁбíÊÇ×óÊäÈëÖÐ
µÄÁм¯£¬ºó¸úÓÒÊäÈë·µ»ØµÄÁеÄÁÐ±í¡£
APPLY ÓÐÁ½ÖÖÐÎʽ£º CROSS APPLY ºÍ OUTER APPLY¡£CROSS APPLY ½ö·µ»ØÍⲿ±íÖÐͨ¹ý±íÖµº¯ÊýÉú³É½á¹û¼¯µÄÐС£OUTER APPLY ¼È·µ
»ØÉú³É½á¹û¼¯µÄÐУ¬Ò²·µ»Ø²»Éú³É½á¹û¼¯µÄÐУ¬ÆäÖбíÖµº¯ÊýÉú³ÉµÄÁÐÖеÄֵΪ NULL¡£
ÀýÈ磬¿¼ÂÇÏÂÁбí Employees ºÍ Departments£º
¸´ÖÆ´úÂë
--Create Employees table and insert values
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' 
Ïà¹ØÎĵµ£º
--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price) --Ëãƽ¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales) --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îСÊý
from titles
go
use pubs
go
select type,sum(price),sum(advance)  ......
ÆäʵÄã¿ÉÒÔʹÓÃÊÂÎñ´¦Àí
±È·½ËµÔÚÒ»¸ö×Ö¶ÎÀïÃæÌí¼ÓÒ»¸öboolean µÄ×ֶε±ÄãÒª´¦Àí¸Ã×ֶεÄʱºò¾Í True ÄÄô±ðµÄÈ˶¼²»¿ÉÒÔ½øÐвÙ×÷
¡¡Èç¹ûÊÇFalse ÄÄô¾Í¿ÉÒÔ½øÐвÙ×÷¡«¡«ºÇ¿É¡«¡«ÎÒÊÇÕâÑùµÄ
¡¡¡¡²»¹ýÄã¿ÉÒÔ¿´¿´¡¡
¡¡SQLËø»úÖÆ
¡¡¡¡ÒÔÏÂÊÇÎÒÕÒµÄһЩÁÏ¡¡Èç¹ûÒªÀí½âSQLËø»úÖÆ ×îºÃ°ÑÏÂÀ´¿´ÍêŶ
ĬÈϵÄÊÂÎñ¸ôÀë¼ ......
string error_syntaxfromSQL, error_create
string new_sql, new_syntax
new_sql = 'SELECT emp_data.emp_id, ' &
& ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£ ......
SQL Server 2005 (MSSQLSERVER) ·þÎñ²»ÄÜÆô¶¯
ÔÒò:VIAÐÒ锸øÆôÓÃÁË£¬Í£ÓÓVIAÐÒé”ÎÊÌâ½â¾ö¡£
"VIAÐÒé"Í£Ó÷½·¨£º¿ªÊ¼->³ÌÐò->Microsoft SQL Server 2005->ÅäÖù¤¾ß->SQL Server Configuration Manager ->´ò¿ªºóÕÒµ½"SQL Server 2005 ÍøÂçÅäÖÃ"->MSSQLSERVER ÊôÐÔµÄÐÒé &nb ......