SQL Server 2005ÖеÄT SQLÔöÇ¿C
ͨÓñí±í´ïʽ Common Table Expressions
ͨÓñí±í´ïʽ£¨CTE£©ÊÇÒ»¸ö¿ÉÒÔÓɶ¨ÒåÓï¾äÒýÓõÄÁÙʱ±íÃüÃûµÄ½á¹û¼¯¡£ÔÚËûÃǵļòµ¥ÐÎʽÖУ¬Äú¿ÉÒÔ½«CTEÊÓΪÀàËÆÓÚÊÓͼºÍÅÉÉú±í»ìºÏ¹¦ÄܵĸĽø°æ±¾¡£ÔÚ²éѯµÄfrom×Ó¾äÖÐÒýÓÃCTEµÄ·½Ê½ÀàËÆÓÚÒýÓÃÅÉÉú±íºÍÊÓͼµÄ·½Ê½¡£Ö»Ð붨ÒåCTEÒ»´Î£¬¼´¿ÉÔÚ²éѯÖжà´ÎÒýÓÃËü¡£ÔÚCTEµÄ¶¨ÒåÖУ¬¿ÉÒÔÒýÓÃÔÚͬһÅú´¦ÀíÖж¨ÒåµÄ±äÁ¿¡£µ«ÊÇCTEµÄÕæÕýÍþÁ¦ÔÚÓÚËüÃǵĵݹ鹦ÄÜ£¬¼´CTE¿ÉÒÔ°üº¬¶ÔËüÃÇ×ÔÉíµÄÒýÓá£
ÊÓͼ¡¢ÅÉÉú±íºÍCTEÄÚ²¿µÄ²éѯµÄÒ»°ãÐÎʽ
1¡¢ÊÓͼ
CREATE VIEW <view_name>(<column_aliases>) AS <view_query>
2¡¢ÅÉÉú±í
SELECT * from (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)
3¡¢CTE
WITH <cte_alias>(<column_aliases>)
AS
{
<cte_query>
)
SELECT * from <cte_alias]>
Ôڹؼü×ÖWITHÖ®ºó£¬ÎªCTEÌṩһ¸ö±ðÃû£¬²¢ÇÒΪËüµÄ½á¹ûÁÐÌṩһ¸ö¿ÉÑ¡µÄ±ðÃûÁÐ±í£»±àдCTEµÄÖ÷Ì壻Ȼºó´ÓÍⲿ²éѯÖÐÒýÓÃËü¡£
with aa(id1,id2 )
as
(select xsrq,count(spbm)
from ecv_saledetail where xsrq>='2010-01-01' and xsrq<='2010-01-10'
group by xsrq)
select * from aa
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
ÔÚSQL Server ÖвåÈëÒ»ÌõÊý¾ÝʹÓÃInsertÓï¾ä£¬µ«ÊÇÈç¹ûÏëÒªÅúÁ¿²åÈëÒ»¶ÑÊý¾ÝµÄ»°£¬Ñ»·Ê¹ÓÃInsert²»½öЧÂʵͣ¬¶øÇһᵼÖÂSQLһϵͳÐÔÄÜÎÊÌâ¡£ÏÂÃæ½éÉÜSQL ServerÖ§³ÖµÄÁ½ÖÖÅúÁ¿Êý¾Ý²åÈë·½·¨£ºBulkºÍ±íÖµ²ÎÊý(Table-Valued Parameters)¡£
ÔËÐÐÏÂÃæµÄ½Å±¾£¬½¨Á¢²âÊÔÊý¾Ý¿âºÍ±íÖµ²ÎÊý¡£
´úÂëÈçÏÂ:
--Create D ......
TOM´óÊåµÄµ÷ÓÅÊéÖÐ˵¹ýÒ»¾ä»°£¬¾ßÌåÓ¢ÎľÍÍüÁË£¬´ó¸ÅÒâ˼¾ÍÊÇ£ºÈç¹ûÓÐÈËÈÃÎÒд±¾ÔõÑùÈÃORACLEÐÔÄÜ×îÂýµÄÊéµÄ»°ÎһὫȡÏû°ó¶¨±äÁ¿(bind variable)×öΪÊéµÄµÚÒ»ÕºÍ×îºóÒ»ÕÂ(ËûµÄÒâ˼ÊÇËûºÜÓÐÓÄĬ~~!)£¬¿É¼û°ó¶¨±äÁ¿µÄÖØÒªÐÔ¡£
°ó¶¨±äÁ¿´ó¶àÓÃÔÚOLTP(online transaction process)ÖУ¬ÔÚOLAP(online analizy process)ÖоÍû± ......
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓбí
SELECT table_name from user_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖÐËùÓÐÓû§ÏµÄËùÓбí
select user,table_name from all_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
select table_name,column_name from user_tab_columns;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
se ......