ÓÃSQL Server 2005 CTE¼ò»¯²éѯ
SQL Server 2005Òý½øÁËÒ»¸öºÜÓмÛÖµµÄеÄTransact-SQLÓïÑÔ×é¼þ£ºÒ»¸öͨÓñí±í´ïʽ£¨Common Table Expression£¬CTE£©£¬ËüÊÇÅÉÉú±íºÍÊÓͼµÄÒ»¸ö±ã½ÝµÄÌæ´ú¡£Í¨¹ýʹÓÃCTE£¬ÎÒÃÇ¿ÉÒÔ´´½¨Ò»¸öÃüÃû½á¹û¼¯À´ÔÚSELECT¡¢INSERT¡¢UPDATEºÍDELETEÓï¾äÖÐÒýÓ㬶øÎÞÐë±£´æ½á¹û¼¯½á¹¹µÄÈκÎÔªÊý¾Ý¡£ÔÚ±¾ÎÄÖУ¬ÎÒ½«²ûÊöÈçºÎÔÚSQL Server 2005Öд´½¨CTE——°üÀ¨ÈçºÎʹÓÃCTEÀ´´´½¨Ò»¸öµÝ¹é²éѯ——²¢¾Ù¼¸¸öÀý×ÓÀ´ËµÃ÷ËüÃÇÊÇÈçºÎʹÓõġ£×¢Ò⣬±¾ÎÄÖÐËùÓÐÀý×Ó¶¼Ê¹ÓÃSQL Server 2005µÄAdventureWorksʾÀýÊý¾Ý¿â¡£
ÔÚSQL Server 2005Öд´½¨Ò»¸ö»ù±¾CTE
ÎÒÃÇ¿ÉÒÔÔÚSELECT¡¢INSERT¡¢UPDATE»òDELETEÓï¾ä֮ǰÌí¼ÓÒ»¸öWITH×Ó¾äÀ´¹¹³ÉÒ»¸öCTE¡£ÏÂÃæµÄÓï·¨ÏÔʾÁËWITH×Ó¾äµÄ»ù±¾¹¹ÔìºÍCTE¶¨Ò壺
[WITH <CTE_definition> [£¬...n]]
<SELECT£¬ INSERT£¬ UPDATE£¬ or DELETE statement that
calls the CTEs>
<CTE_definition>::=
CTE_name [(column_name [£¬...n ])]
AS
(
CTE_query
)
ÈçÉÏÃæÓï¾äËùʾ£¬Äã¿ÉÒÔÔÚ¿ÉÑ¡µÄWITH×Ó¾äÖж¨Òå¶à¸öCTE¡£CTE¶¨Òå°üº¬CTEÃû³Æ¡¢CTE×Ö¶ÎÃû³Æ¡¢AS¹Ø¼ü×ÖºÍÀ¨ºÅÖеÄCTE²éѯ¡£×¢Ò⣬CTE×Ö¶ÎÃû³ÆµÄÊýÄ¿±ØÐëÓëCTE²éѯ·µ»ØµÄ×Ö¶ÎÊýÄ¿ÏàÆ¥Åä¡£ÁíÍ⣬Èç¹ûCTE²éѯÌṩËùÓÐ×Ö¶ÎÃû³Æ£¬ÄÇô×Ö¶ÎÃû³ÆÊÇ¿ÉÑ¡µÄ¡£
ÏÖÔÚÎÒÃÇÒѾ¶ÔSQL ServerµÄCTEÓï·¨ÓÐÁË»ù±¾µÄÁ˽⣬ÏÂÃæÈÃÎÒÃÇÀ´¿´Ò»¸öCTE¶¨ÒåµÄÀý×Ó£¬ÒÔ±ã¸üºÃµØÀí½âÕâ¸öÓï·¨¡£ÏÂÃæµÄÀý×Ó¶¨ÒåÁËÒ»¸öÃüÃûΪProductSold µÄCTE£¬½Ó×ÅÔÚSELECTÓï¾äÖÐÒýÓÃÁËCTE£º
WITH ProductSold (ProductID£¬ TotalSold)
AS
(
SELECT ProductID£¬ SUM(OrderQty)
from Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.ProductID£¬ p.Name£¬ p.ProductNumber£¬
ps.TotalSold
from Production.Product AS p
INNER JOIN ProductSold AS ps
ON p.ProductID = ps.ProductID
ÕâÀï¿ÉÒÔ¿´µ½£¬WITH×Ó¾äÔÚÒýÓÃCTEµÄSELECTÓï¾ä֮ǰ¡£WITH×Ó¾äµÄµÚÒ»Ðаüº¬ÁËCTEµÄÃû³Æ£¨ProductSold£©ÒÔ¼°ÔÚCTE µÄÁ½¸ö×Ö¶ÎÃû³Æ(ProductID ºÍTotalSold)¡£½Ó×ÅÊÇAS¹Ø¼ü×Ö£¬½ô½Ó×ÅÊÇÀ¨ºÅÖеÄCTE²éѯ¡£ÕâÑù£¬CTE²éѯ·µ»ØÁËÿ¸ö²úÆ·µÄÏúÊÛ×ÜÊý¡£
Ïà¹ØÎĵµ£º
1 :ÆÕͨSQLÓï¾ä¿ÉÒÔÓÃExecÖ´ÐÐ
Àý: Select * from tableName
Exec('select * from tableName')
& ......
SQL ÊÇÒ»ÃÅ ANSI µÄ±ê×¼¼ÆËã»úÓïÑÔ£¬ÓÃÀ´·ÃÎʺͲÙ×÷Êý¾Ý¿âϵͳ¡£SQL Óï¾äÓÃÓÚÈ¡»ØºÍ¸üÐÂÊý¾Ý¿âÖеÄÊý¾Ý¡£SQL ¿ÉÓëÊý¾Ý¿â³ÌÐòÐͬ¹¤×÷£¬±ÈÈç MS Access¡¢DB2¡¢Informix¡¢MS SQL Server¡¢Oracle¡¢Sybase ÒÔ¼°ÆäËûÊý¾Ý¿âϵͳ¡£
²»ÐÒµØÊÇ£¬´æÔÚןܶ಻ͬ°æ±¾µÄ SQL ÓïÑÔ£¬µ«ÊÇΪÁËÓë ANSI ±ê×¼Ïà¼æÈÝ£¬ËüÃDZØÐëÒÔÏàËÆµÄ ......
ʹÓÃÕûÊýÊý¾ÝµÄ¾«È·Êý×ÖÊý¾ÝÀàÐÍ¡£
bigint ´Ó -2^63 (-9223372036854775808) µ½ 2^63-1 (9223372036854775807) µÄÕûÐÍÊý¾Ý£¨ËùÓÐÊý×Ö£©¡£
´æ´¢´óСΪ 8 ¸ö×Ö½Ú¡£
int ´Ó -2^31 (-2,147,483,648) µ½ 2^31 - 1 (2,147,483,64 ......
ÔÎÄ£ºhttp://hi.baidu.com/scoful/blog/item/963339d793cfe92506088b11.html
ÈçºÎ´´½¨ SQL Server 2005 ÐÂÕË»§£¿
Ê×ÏÈÒªÉèÖÃSQL Server 2005µÄ·ÃÎÊ·½Ê½
1´ò¿ªSQL Server Management Studio
2·þÎñÆ÷Ãû³ÆÉÏÓÒ¼üÑ¡Ôñ“ÊôÐÔ”£¬´ò¿ª“·þÎñÆ÷ÊôÐÔ”¶Ô»°¿ò£¬ÔÚ“Ñ¡ÔñÒ³”ÀïÑ¡Ôñ“° ......
ÎÒÃÇÔÚ±àдMISϵͳºÍWebÓ¦ÓóÌÐòµÈϵͳʱ£¬¶¼Éæ¼°µ½ÓëÊý¾Ý¿âµÄ½»»¥£¬Èç¹ûÊý¾Ý¿âÖÐÊý¾ÝÁ¿ºÜ´óµÄ»°£¬Ò»´Î¼ìË÷ËùÓеļǼ£¬»áÕ¼ÓÃϵͳºÜ´óµÄ×ÊÔ´£¬Òò´ËÎÒÃdz£³£²ÉÓã¬ÐèÒª¶àÉÙÊý¾Ý¾ÍÖ»´ÓÊý¾Ý¿âÖÐÈ¡¶àÉÙÌõ¼Ç¼£¬¼´²ÉÓ÷ÖÒ³Óï¾ä¡£¸ù¾Ý×Ô¼ºÊ¹ÓùýµÄÄÚÈÝ£¬°Ñ³£¼ûÊý¾Ý¿âSQL Server,OracleºÍMySQLµÄ·ÖÒ³Óï¾ä£¬´ÓÊý¾Ý¿â±íÖÐµÄµÚ ......