ÉèΪÊ×Ò³ ¼ÓÈëÊÕ²Ø

TOP

SQLµãµÎ10¨DʹÓÃwithÓï¾äÀ´Ð´Ò»¸öÉÔ΢¸´ÔÓsqlÓï¾ä£¬¸½¼ÓºÍ×Ó²éѯµÄÐÔÄܶԱÈ
2014-11-24 07:41:56 À´Ô´: ×÷Õß: ¡¾´ó ÖРС¡¿ ä¯ÀÀ:1´Î
Tags£ºSQLµãµÎ ʹÓà with Óï¾ä Ò»¸ö ÉÔ΢ ¸´ÔÓ sql ¸½¼Ó ²éѯ ÐÔÄÜ ¶Ô±È
½ñÌìż¶û¿´µ½sqlÖÐÒ²ÓÐwith¹Ø¼ü×Ö£¬ºÃ´õҲдÁ˼¸ÄêµÄsqlÓï¾ä£¬¾ÓÈ»µÚÒ»´Î½Ó´¥£¬ÎÞÖª°¡¡£¿´ÁËһ벩Ö÷µÄÎÄÕ£¬×Ô¼ºÌí¼ÓÁËһЩÄÚÈÝ£¬×öÁ˼òµ¥µÄ×ܽᣬÕâ¸öÓï¾ä»¹ÊǵÚÒ»´Î¼ûµ½£¬Ñ§Ï°ÁË¡£ÎÒ´Ó¼òµ¥µ½¸´ÔÓµØÐ´£¬Ï£Íû¸ßÊÖÃDz»Òª¼ûЦ¡£ÏÂÃæµÄsqlÓï¾äÉè¼Æµ½Èý¸ö±í£¬±íµÄÄÚÈÝÎÒÓÃtxtÎļþ¸´ÖƽøÈ¥£¬ÕâÀï²»·ÁʹÓÃÉÏÒ»¸öËæ±Ê½éÉܵĽ¨Á¢¶Ëµ½¶ËµÄpackageµÄ·½·¨½«ÕâЩ±íµ¼Èëµ½Êý¾Ý¿âÖУ¬¾ßÌåµÄ¾Í²»ËµÁË¡£

´ÓÕâÀïÏÂÔØÎļþemployees.txt£¬customers.txt£¬orders.txt

²Î¿¼ÎÄÕÂ:http://www.2cto.com/database/201105/91595.html

ʹÓÃpackageµ¼ÈëÊý¾Ý:http://www.2cto.com/database/201105/91597.html

¼òµ¥µÄ¾ÛºÏ

´Óorders±íÖÐÑ¡Ôñ¸÷¸öÄê·Ý¹²Óй²ÓжàÉÙ¿Í»§¶©¹ºÁËÉÌÆ·

  • µÚÒ»ÖÖд·¨£¬ÎÒÃÇ¿ÉÒÔд³ÉÕâÑù
         
    1 select YEAR (o.orderdate) orderyear, COUNT ( distinct (custid)) numCusts
    2 from Sales.Orders o
    3 group by YEAR (o.orderdate)
    4 go
    ҪעÒâµÄÊÇÈç¹û°Ñgroup by YEAR(o.orderdata)»»³Égroup by orderyear¾Í»á³ö´í£¬ÕâÀïÉæ¼°µ½sqlÓï¾äµÄÖ´ÐÐ˳ÐòÎÊÌ⣬ÓÐʱ¼äÔÙÁ˽âһϡ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡
  • µÚ¶þÖÖд·¨£¬
         
    1 select orderyear, COUNT ( distinct (custid))numCusts
    2 from ( select YEAR (orderdate) as orderyear,custid from sales.orders) as D
    3 group by orderyear
    4 go
    ÔÚfromÓï¾äÖÐÏȵõ½orderyear£¬È»ºóÔÙselectÓï¾äÖоͲ»»á³öÏÖûÓÐÕâ¸ö×ֶεĴíÎóÁË
  • µÚÈýÖÖд·¨£¬
         
    1 select orderyear, COUNT ( distinct (custid)) numCusts
    2 from ( select YEAR (orderdate),custid from sales.orders) as D(orderyear,custid)
    3 group by orderyear
    4 go
    ÔÚas DºóÃæ¼ÓÉÏÑ¡Ôñ³öµÄ×ֶΣ¬ÊDz»ÊǸü¼ÓµÄÇå³þÃ÷ÁËÄØ£¡
  • µÚËÄÖÖд·¨£¬with³ö³¡ÁË
         
    1 with c as (
    2 select YEAR (orderdate) orderyear, custid from sales.orders)
    3 select orderyear, COUNT ( distinct (custid)) numCusts from c group by orderyear
    4 go
    with¿ÉÒÔʹÓï¾ä¸ü¼ÓµÄ¾­´Õ£¬ÏÂÃæÊÇȨÍþ½âÊÍ¡£¡¡¡¡
    ¡¡¡¡¡¡¡¡
    Ö¸¶¨ÁÙʱÃüÃûµÄ½á¹û¼¯£¬ÕâЩ½á¹û¼¯³ÆÎª¹«Óñí±í´ïʽ (CTE)¡£¸Ã±í´ïʽԴ×Ô¼òµ¥²éѯ£¬²¢ÇÒÔÚµ¥Ìõ SELECT¡¢INSERT¡¢UPDATE¡¢MERGE »ò DELETE Óï¾äµÄÖ´Ðз¶Î§ÄÚ¶¨Òå¡£¸Ã×Ó¾äÒ²¿ÉÓÃÔÚ CREATE VIEW Óï¾äÖУ¬×÷Ϊ¸ÃÓï¾äµÄ SELECT ¶¨ÒåÓï¾äµÄÒ»²¿·Ö¡£¹«Óñí±í´ïʽ¿ÉÒÔ°üÀ¨¶Ô×ÔÉíµÄÒýÓá£ÕâÖÖ±í´ïʽ³ÆÎªµÝ¹é¹«Óñí´ïʽ¡£¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡----MSDN

  • µÚÎåÖÖд·¨£¬Ò²¿ÉÒÔ½è¼øµÚÈýÖÖд·¨£¬ÕâÑùʹÓï¾ä¸ü¼ÓÇå³þÃ÷ÁË£¬±ãÓÚά»¤
         
    1 with c(orderyear,custid) as (
    2 select YEAR (orderdate),custid from sales.orders)
    3 select orderyear, COUNT ( distinct (custid)) numCusts from c group by
  •      
    c.orderyear
    4 go
    ÉÏÃæ5ÖÐд·¨¶¼µÃµ½ÏàͬµÄ½á¹û£¬ÈçÏÂͼ1£ºÍ¼1

Ìí¼Ó¼ÆËã

  • ÏÖÔÚÒªÇóÒªÇó¼ÆËã³ö¶©µ¥±íÖÐÿÄê±ÈÉÏÒ»ÄêÔö¼ÓµÄ¿Í»§ÊýÄ¿£¬Õâ¸öÉÔ΢¸´ÔÓ
  • 1 with
¡¾´ó ÖРС¡¿¡¾´òÓ¡¡¿ ¡¾·±Ìå¡¿¡¾Í¶¸å¡¿¡¾Êղء¿ ¡¾ÍƼö¡¿¡¾¾Ù±¨¡¿¡¾ÆÀÂÛ¡¿ ¡¾¹Ø±Õ¡¿ ¡¾·µ»Ø¶¥²¿¡¿
·ÖÏíµ½: 
ÉÏһƪ£ºredisѧϰ±Ê¼ÇÖ®pipeline ÏÂһƪ£ºSQLµãµÎ11¨DÖØÖÃwin7µÇ¼ÃÜÂëÓ°Ï..

ÆÀÂÛ

ÕÊ¡¡¡¡ºÅ: ÃÜÂë: (ÐÂÓû§×¢²á)
Ñé Ö¤ Âë:
±í¡¡¡¡Çé:
ÄÚ¡¡¡¡ÈÝ:

¡¤HyperText Transfer (2025-12-26 07:20:48)
¡¤°ëСʱ¸ã¶® HTTP¡¢HT (2025-12-26 07:20:42)
¡¤CPythonÊÇʲô£¿PyPy (2025-12-26 06:50:09)
¡¤Python|ÈçºÎ°²×°seab (2025-12-26 06:50:06)
¡¤pythonҪѧϰÊý¾Ý·Ö (2025-12-26 06:50:03)