SQL With cube
ÏÐÀ´Ð´ÏÂwith cubeµÄÓ÷¨
cubeÔËËã·ûÔÚ SELECT Óï¾äµÄ GROUP BY ×Ó¾äÖÐÖ¸¶¨¡£¸ÃÓï¾äµÄÑ¡ÔñÁбíÓ¦°üº¬Î¬¶ÈÁк;ۺϺ¯Êý±í´ïʽ¡£GROUP BY Ó¦Ö¸¶¨Î¬¶ÈÁк͹ؼü×Ö WITH CUBE¡£½á¹û¼¯½«°üº¬Î¬¶ÈÁÐÖи÷ÖµµÄËùÓпÉÄÜ×éºÏ£¬ÒÔ¼°ÓëÕâЩά¶ÈÖµ×éºÏÏàÆ¥ÅäµÄ»ù´¡ÐÐÖеľۺÏÖµ¡£
ÏÈ¿´ÏÂ±í£º
ÎÒÃÇÒÔid¾ÛºÏ²éѯ³öƽ¾ù·Ö
ÕâÒ»ÌõSQLÓï¾äÓëÉÏÃæÎ¨Ò»²»Í¬Ö®´¦¼´Îª¶àÁ˺óÃæµÄ with cube£¬¶ø½á¹ûÈ´ÊDZÈÇ°Ãæ¸ü¶àÁËÒ»Ìõ½á¹û£¬ËüͬÑùÊǰ´ÀàIDµÄƽ¾ù·Ö£¬²»Í¬ÊÇÓÖ¶àÁË×îºóÒ»¸ö½á¹ûnull 4£¬Õâ¸öÊÇÊ²Ã´ÄØ£¬ÎÒÃÇÀ´¿´µÚÒ»¶Î£¬with cube ÊÇ °üº¬ËùÓпÉÄܵÄ×éºÏ£¬ÎÒÃǸù¾ÝID·Ö£¬Ëü°ïÎÒÃǰ´ID²é³öƽ¾ù×Ü£¬Í¬Ê±ÔÙ°ïÎÒÃǼÆËã³öËùÓÐµÄÆ½¾ù·Ö£¬¼´¸Ã±íIDΪ1Óë2 µÄƽ¾ù·Ö¡£Õâ¸öÎÒÃÇ¿ÉÒÔÔÚºÜ¶àµØ·½Ó¦Óã¬Èç°´ÐÔ±ðͳ¼Æ¹¤×Ê£¬°´²¿ÃÅͳ¼ÆÒµ¼¨……
Æäʵʵ¼ÊÖУ¬ÎÒÃÇÍùÍù¾ÍµÃµ½¸ü׼ȷµÄÊý¾Ý£¬¼´¡«ÉÏÃæ½á¹ûÓ¦¸ÃÒÔСÊýÐÎʽΪºÃ¡«ÆäʵÉÏÃæ½á¹ûÒ²ÊDz»×¼È·µÄ£¬SQL½«ÆäËÄÉáÎåÈëÁË¡«
ÎÒÃÇʽʽ°ì·¨¡«
ÎÒÃÇ·¢ÏÖÓÐÁË£¬ÓÐÁËСÊý£¬µ«ÔÙϸÐÄÒ»¿´£¬½á¹ûÊDz»¶ÔµÄ£¬ÕâÑùSQLÏ൱Óھͽ«Ç°Ò»²½µÄ²Ù×÷½á¹ûת»»³ÉСÊý£¬Ö±½ÓÔÚ4ºóÃæ¼ÓÁ˶þ¸ö0£¬ÕâÏÔʾ²»¶Ô£¬ÄÇôSQLΪʲô»áÕâÑù£¬ÎÒÃÇÓÖÈçºÎ½â¾ö£¬ÆäʵÔÚ±ívoteDetailsÖУ¬ÎÒÃǵÄvoteNum×Ö¶ÎΪintÐÍ£¬ÄÇôµ±ÎÒÃÇÒÔavg(voteNum)¼ÆËãʱ£¬SQL¾Í»áÒÔvoteNumµÄÊý¾ÝÀàÐͼÆË㣬Ҳ¼´Îªint£¬ËùÒÔ²Ù×÷½á¹ûΪintÐÍ£¬»áËÄÉáÎåÈ룬ÕâÑùËäÈ»ÎÒÃÇcast(avg(voteNum) as decimal(20,2)),µ«Ò²½ö½öÊǽ«avg¼ÆËãÖ®ºóµÄÕûÐÎת»»Îªdecimail(20,2)±£Áô¶þλСÊý¡£
ÖªµÀÕâ¸öÔÀí£¬ÎÒÃǾͺðìÁË£¡ÔÚavg¼ÆËã֮ǰ½«Êý¾ÝÀàÐÍת»»¡£Èçϳ¢ÊÔ£º
µÃµ½Ô¤ÆÚ½á¹û£¬Ð¡Êý²»ÊǶþλû¹ØÏµ£¬ÎÒÃÇ¿ÉÒÔ ÔÚavg¼ÆËãºóµÄ½á¹ûÔÙת»»Ò»´Î£¬±£Áô¶þÃÇСÊý£¡
Ïà¹ØÎĵµ£º
SQLÓï·¨ÊÖ²á
Select
ÓÃ;£º
´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨µÄÁеÄÊý¾Ý
Óï·¨£º
SELECT column_name(s) from table_name
½âÊÍ£º
´ÓÊý¾Ý¿âÖÐѡȡ×ÊÁÏÁУ¬²¢ÔÊÐí´ÓÒ»»ò¶à¸ö×ÊÁϱíÖУ¬Ñ¡È¡Ò»»ò¶à¸ö×ÊÁÏÁлò×ÊÁÏÐС£SELECT ³ÂÊöʽµÄÍêÕûÓï·¨Ï൱¸´ÔÓ£¬µ«Ö÷Òª×Ó¾ä¿ÉժҪΪ£º
SELECT select_ ......
select 1 from table;Óëselect anycol(Ä¿µÄ±í¼¯ºÏÖеÄÈÎÒâÒ»ÐУ© from table;Óëselect * from table ´Ó×÷ÓÃÉÏÀ´ËµÊÇûÓвî±ðµÄ£¬¶¼ÊDz鿴ÊÇ·ñÓмǼ£¬Ò»°ãÊÇ×÷Ìõ¼þ²éѯÓõġ£select 1 from ÖеÄ1ÊÇÒ»³£Á¿£¨¿ÉÒÔΪÈÎÒâÊýÖµ£©£¬²éµ½µÄËùÓÐÐеÄÖµ¶¼ÊÇËü£¬µ«´ÓЧÂÊÉÏÀ´Ëµ£¬1>anycol>*£¬ÒòΪ²»Óòé×Öµä±í¡£ ......
SQL Server 7.0 ÈëÃÅ(7)
ʱ¼ä£º2001/02/06 11:57 ×÷Õߣº¶¹¶¹ CHINAASP
¡¡ &nbs ......
¡¶SQL Sever 2005 Êý¾Ý¿â»ù´¡¼°Ó¦Óü¼Êõ½Ì³ÌÓëʵѵ ¡· ÖÜÆæ
SQL Server 2005µÄ°æ±¾£º
1. SQL Server 2005 Enterprise Edition ÆóÒµ°æ(32λºÍ64λ)
ÊÊÓÃÓÚ³¬´óÐÍÆóÒµ¡£Enterprise Edition ÊÇ×îÈ«ÃæµÄSQL Server°æ±¾£¬Êdz¬´óÐÍÆóÒµµÄÀíÏëÑ¡Ôñ£¬Äܹ»Âú×ã×ÔÓµÄÒªÇ󡣸ð汾»¹Í ......