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

SQL ÖÐWith as µÄÓ÷¨

Ò»£®WITH ASµÄº¬Òå
WITH AS¶ÌÓҲ½Ð×ö×Ó²éѯ²¿·Ö£¨subquery factoring£©£¬¿ÉÒÔÈÃÄã×öºÜ¶àÊÂÇ飬¶¨ÒåÒ»¸öSQLƬ¶Ï£¬¸ÃSQLƬ¶Ï»á±»Õû¸öSQLÓï¾äËùÓõ½¡£ÓеÄʱºò£¬ÊÇΪÁËÈÃSQLÓï¾äµÄ¿É¶ÁÐÔ¸ü¸ßЩ£¬Ò²ÓпÉÄÜÊÇÔÚUNION ALLµÄ²»Í¬²¿·Ö£¬×÷ΪÌṩÊý¾ÝµÄ²¿·Ö¡£
Ìرð¶ÔÓÚUNION ALL±È½ÏÓÐÓá£ÒòΪUNION ALLµÄÿ¸ö²¿·Ö¿ÉÄÜÏàͬ£¬µ«ÊÇÈç¹ûÿ¸ö²¿·Ö¶¼È¥Ö´ÐÐÒ»±éµÄ»°£¬Ôò³É±¾Ì«¸ß£¬ËùÒÔ¿ÉÒÔʹÓÃWITH AS¶ÌÓÔòÖ»ÒªÖ´ÐÐÒ»±é¼´¿É¡£Èç¹ûWITH AS¶ÌÓïËù¶¨ÒåµÄ±íÃû±»µ÷ÓÃÁ½´ÎÒÔÉÏ£¬ÔòÓÅ»¯Æ÷»á×Ô¶¯½«WITH AS¶ÌÓïËù»ñÈ¡µÄÊý¾Ý·ÅÈëÒ»¸öTEMP±íÀÈç¹ûÖ»ÊDZ»µ÷ÓÃÒ»´Î£¬Ôò²»»á¡£¶øÌáʾmaterializeÔòÊÇÇ¿Öƽ«WITH AS¶ÌÓïÀïµÄÊý¾Ý·ÅÈëÒ»¸öÈ«¾ÖÁÙʱ±íÀï¡£ºÜ¶à²éѯͨ¹ýÕâÖÖ·½·¨¶¼¿ÉÒÔÌá¸ßËٶȡ£
¶þ£®Ê¹Ó÷½·¨
ÏÈ¿´ÏÂÃæÒ»¸öǶÌ׵IJéѯÓï¾ä£º
select * from person.StateProvince where CountryRegionCode in
(select CountryRegionCode from person.CountryRegion where Name like 'C%')
ÉÏÃæµÄ²éѯÓï¾äʹÓÃÁËÒ»¸ö×Ó²éѯ¡£ËäÈ»ÕâÌõSQLÓï¾ä²¢²»¸´ÔÓ£¬µ«Èç¹ûǶÌ׵IJã´Î¹ý¶à£¬»áʹSQLÓï¾ä·Ç³£ÄÑÒÔÔĶÁºÍά»¤¡£Òò´Ë£¬Ò²¿ÉÒÔʹÓñí±äÁ¿µÄ·½Ê½À´½â¾öÕâ¸öÎÊÌ⣬SQLÓï¾äÈçÏ£º
declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like 'C%')
select * from person.StateProvince where CountryRegionCode
in (select * from @t)
ËäÈ»ÉÏÃæµÄSQLÓï¾äÒª±ÈµÚÒ»ÖÖ·½Ê½¸ü¸´ÔÓ£¬µ«È´½«×Ó²éѯ·ÅÔÚÁ˱í±äÁ¿@tÖУ¬ÕâÑù×ö½«Ê¹SQLÓï¾ä¸üÈÝÒ×ά»¤£¬µ«ÓÖ»á´øÀ´ÁíÒ»¸öÎÊÌ⣬¾ÍÊÇÐÔÄܵÄËðʧ¡£ÓÉÓÚ±í±äÁ¿Êµ¼ÊÉÏʹÓÃÁËÁÙʱ±í£¬´Ó¶øÔö¼ÓÁ˶îÍâµÄI/O¿ªÏú£¬Òò´Ë£¬±í±äÁ¿µÄ·½Ê½²¢²»Ì«ÊʺÏÊý¾ÝÁ¿´óÇÒƵ·±²éѯµÄÇé¿ö¡£Îª´Ë£¬ÔÚSQL Server 2005ÖÐÌṩÁËÁíÍâÒ»ÖÖ½â¾ö·½°¸£¬Õâ¾ÍÊǹ«Óñí±í´ïʽ£¨CTE£©£¬Ê¹ÓÃCTE£¬¿ÉÒÔʹSQLÓï¾äµÄ¿Éά»¤ÐÔ£¬Í¬Ê±£¬CTEÒª±È±í±äÁ¿µÄЧÂʸߵöࡣ
ÏÂÃæÊÇCTEµÄÓï·¨£º
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
ÏÖÔÚʹÓÃCTEÀ´½â¾öÉÏÃæµÄÎÊÌ⣬SQLÓï¾äÈçÏ£º
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.StateProvince where CountryRegionCode in (select * from c


Ïà¹ØÎĵµ£º

SQL Server 2008³É¾ÍÆóÒµÊý¾Ýƽ̨

 
 
±¾ÎĹؼü´Ê£ºSQL 2008 SQL SQL Server SQL Server 2008
¶ÔÓÚ¼´½«ÔÚ2008Äê¡ÖØ·¢²¼µÄSQL Server 2008£¬Î¢ÈíÆÚÍûÊг¡²»½ö½öÊÇ°ÑËü×÷Ϊ¹ØϵÊý¾Ý¿âϵͳÀ´¿´´ý£¬Î¢Èí½«Æ䶨λΪһ¸öÆóÒµÊý¾Ýƽ̨£¬ËäÈ»¹ØϵÊý¾Ý¿âÒýÇæÈÔÈ»ÊÇSQL Server 2008µÄºËÐÄ£¬²»¹ýSQL Server 2008ËùÄÜÌṩµÄ·þÎñ¹ã¶È½«Ô¶³¬¹ý¼òµ¥µÄ¹ØϵÊý¾ ......

SQL Server 2008¼¸ÏîÐÂÌØÐÔ¸ÅÊö

¼Æ»®ÓÚ2008ÄêµÚÒ»¼¾¶È·¢²¼µÄSQL Server 2008²»½ö¶ÔÔ­ÓÐÐÔÄܽøÐÐÁ˸Ľø£¬»¹Ìí¼ÓÁËÐí¶àÐÂÌØÐÔ£¬±ÈÈçÐÂÌíÁËÊý¾Ý¼¯³É¹¦ÄÜ£¬¸Ä½øÁË·ÖÎö·þÎñ£¬±¨¸æ·þÎñ£¬ÒÔ¼°Office¼¯³ÉµÈµÈ¡£
¡¡¡¡SQL Server¼¯³É·þÎñ
¡¡¡¡SSIS(SQL Server¼¯³É·þÎñ)ÊÇÒ»¸öǶÈëʽӦÓóÌÐò£¬ÓÃÓÚ¿ª·¢ºÍÖ´ÐÐETL(½âѹËõ¡¢×ª»»ºÍ¼ÓÔØ)°ü¡£SSIS´úÌæÁËSQL 2000µÄDTS¡£Õ ......

SQL Server 2008³É¾ÍÆóÒµÊý¾Ýƽ̨

 
 
±¾ÎĹؼü´Ê£ºSQL 2008 SQL SQL Server SQL Server 2008
¶ÔÓÚ¼´½«ÔÚ2008Äê¡ÖØ·¢²¼µÄSQL Server 2008£¬Î¢ÈíÆÚÍûÊг¡²»½ö½öÊÇ°ÑËü×÷Ϊ¹ØϵÊý¾Ý¿âϵͳÀ´¿´´ý£¬Î¢Èí½«Æ䶨λΪһ¸öÆóÒµÊý¾Ýƽ̨£¬ËäÈ»¹ØϵÊý¾Ý¿âÒýÇæÈÔÈ»ÊÇSQL Server 2008µÄºËÐÄ£¬²»¹ýSQL Server 2008ËùÄÜÌṩµÄ·þÎñ¹ã¶È½«Ô¶³¬¹ý¼òµ¥µÄ¹ØϵÊý¾ ......

SQL Server JDBC Çý¶¯·â×°

1¡¢½¨Á¢Ò»¸öjdbc.jsp Îļþ£º
2¡¢½«ÏÂÃæÏàÓ¦µÄ´úÂ븴ÖƵ½jdbc.jspÖÐ
3¡¢±àдҪÁ¬½Óµ½Êý¾Ý¿âµÄjspÎļþʱ£¬Ö»Ð轫jdbc.jspÎļþ°üº¬µ½¸ÃjspÎļþÖоÍÐÐÁË
·½·¨£ºÌí¼Ó<%@  include file="jdbc.jsp">µ½jspÎļþÖм´¿É
//sql server2005:
<%@ page language="java" import ="java.util.*"%>
 
<%
& ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ