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_ ......
µ¼³ö.sqlÎļþ
1.½«Êý¾Ý¿âtransfer_server_dbµ¼³öµ½transfSRV.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db > /home/eric/transfSRV.sql
2.½«Êý¾Ý¿âtransfer_server_dbÖеÄdevice_info_tableµ¼³öµ½table.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db devi ......
Another Look at MySQL 5.1's SQL Diagnostic Tools
http://dev.mysql.com/tech-resources/articles/mysql_51_diagnostic_tools.html Êý¾Ý¿âµÄÐÔÄܾö¶¨ÒòËØ£º
£¨1£©Êý¾Ý¿âµÄÉè¼Æ
£¨2£©SQL´úÂë ¿ÉÒÔÓÃÒÔϼ¸ÖÖ·½·¨À´½â¾öһЩÐÔÄÜÎÊÌ⣺
£¨1£©Ë÷Òý
£¨2£©Ë®Æ½·ÖÇø
£¨3£©ÁÐÊÊÓ¦µÄ±í
colum ......
×Ô£ºhttp://blog.csdn.net/AJAXBloger/archive/2007/08/29/1764506.aspx
×Ó²éѯ£º
ʹÓÃ×Ó²éѯµÄÔÔò
1.Ò»¸ö×Ó²éѯ±ØÐë·ÅÔÚÔ²À¨ºÅÖС£
2.½«×Ó²éѯ·ÅÔڱȽÏÌõ¼þµÄÓÒ±ßÒÔÔö¼Ó¿É¶ÁÐÔ¡£
×Ó²é
ѯ²»°üº¬ ORDER BY ×Ӿ䡣¶ÔÒ»¸ö SELECT Óï¾äÖ»ÄÜÓÃÒ»¸ö ORDER BY ×Ӿ䣬
²¢ÇÒÈç¹ûÖ¸¶¨ÁËËü¾Í±ØÐë·ÅÔÚÖ÷
SELECT Óï¾äµÄ×î ......
×Ó²éѯ£º
ʹÓÃ×Ó²éѯµÄÔÔò
1.Ò»¸ö×Ó²éѯ±ØÐë·ÅÔÚÔ²À¨ºÅÖС£
2.½«×Ó²éѯ·ÅÔڱȽÏÌõ¼þµÄÓÒ±ßÒÔÔö¼Ó¿É¶ÁÐÔ¡£
×Ó²éѯ²»°üº¬ ORDER BY ×Ӿ䡣¶ÔÒ»¸ö SELECT Óï¾äÖ»ÄÜÓÃÒ»¸ö ORDER BY ×Ӿ䣬
²¢ÇÒÈç¹ûÖ¸¶¨ÁËËü¾Í±ØÐë·ÅÔÚÖ÷ SELECT Óï¾äµÄ×îºó¡£
ORDER BY ×Ó¾ä¿ÉÒÔʹÓ㬲¢ÇÒÔÚ½øÐÐ Top-N ·ÖÎöʱÊDZØÐëµÄ¡£
3.ÔÚ×Ó² ......