mysqlÖÐGROUP BY½áºÏGROUP_CONCATµÄʹÓà [ת]
http://javeye.javaeye.com/blog/558093
ÎÒÃÇÖªµÀ£¬group by¿ÉÒÔ½«sql²éѯ½á¹û°´ÕÕgroup byºóÃæÁнøÐзÖÀàÏÔʾ¡£±ÈÈ磺
Sql´úÂë
select
columnA,columnB
from
table
group
by
columnA,columnB
select columnA,columnB from table group by columnA,columnB
Ôò²éѯ½á¹û½«°´ÕÕcolumnAºÍcolumnB·ÖÀàÏÔʾ¡£Ã»ÓÐÏÔʾÔÚgroup byÖеÄÁв»ÄÜÖ±½Ó×÷Ϊ·µ»ØÁзÅÔÚsqlÓï¾äÖУ¬±ÈÈçÈçÏÂsql¾ÍÊDz»ÕýÈ·µÄ
Sql´úÂë
select
columnA,columnC
from
table
group
by
columnA
select columnA,columnC from table group by columnA
ÓÉÓÚcolumnC²»ÔÚgroup byµÄ·¶Î§Ö®À࣬ËùÒÔÕâÑùдÊDz»¶ÔµÄ£¬ËùÐÒµÄÊÇ£¬group byÖ§³ÖһЩsql º¯ÊýµÄʹÓ㬱ÈÈçSUM,AVG,COUNTµÈµÈ¡£ÕâЩ¶¼±È½Ï³£Ó㬽ñÌìÎÒÒª¼Ç¼ÏµÄÊÇÕâ¸ö²»³£ÓõÄGROUP_CONCAT¡£
ÓÐÒ»¸öÐèÇó£¬ÐèÒªÓõ½group by ²ÅÄÜʵÏÖ£¬¿ÉÊÇ£¬ÎÒͬÊÇ»¹ÐèÒª·µ»ØijÁеÄËùÓнá¹û£¬£¨×¢Ò⣬²»ÊÇ×öavg£¬sumµÈ²Ù×÷£¬ÎÒҪö¾ÙÕâÁеÄËùÓнá¹û£©£¬ÄÇô¾Í¿ÉÒÔÓõ½GROUP_CONCAT¡£
¾Ù¸öÀý×Ó£º
ÎÒÓÐÒ»ÕÅÊý¾Ý¿â±í½á¹¹ÈçÏ£º
ÁÐÃû
º¬Òå
year
Äê·Ý
month
Ô·Ý
volumn
ÆÚÊý
¸Ã±í´æ´¢ÁËijÔÓÖ¾µÄÄê·Ý£¬Ô·ݺÍÆÚÊý¡£Èç¹ûÐèÇó¶Ô¸Ã±íÄÚÈÝ×÷ÈçÏÂÏÔʾ£º
2010Äê12ÔÂ
µÚ1ÆÚ µÚ2ÆÚ µÚ3ÆÚ µÚ4ÆÚ
2010Äê11ÔÂ
µÚ1ÆÚ µÚ2ÆÚ µÚ3ÆÚ µÚ4ÆÚ µÚ5ÆÚ
2010Äê10ÔÂ
µÚ1ÆÚ µÚ2ÆÚ µÚ3ÆÚ µÚ4ÆÚ
2010Äê9ÔÂ
µÚ1ÆÚ µÚ2ÆÚ µÚ3ÆÚ µÚ4ÆÚ µÚ5ÆÚ
2010Äê8ÔÂ
µÚ1ÆÚ µÚ2ÆÚ µÚ3ÆÚ µÚ4ÆÚ
sql¸ÃÔõôдÄØ£¿°´ÕÕÄê·ÝºÍÔ·Ý×ögroup by£¿È»ºó°´ÕÕÄê·ÝºÍÔ·Ý×öµ¹ÐðÅÅÁУ¿
Sql´úÂë
select
year
,
month
from
magazine
group
by
year
,
month
order
by
year
desc
,
month
desc
select year,month from magazine group by year,month order by year desc,month desc
ÄǾßÌåµÄÆÚÊýÐÅÏ¢¾Í¶ªÁË£¿Äܲ»ÄÜ×ögroup byµÄʱºò£¬»¹ÄÜ·µ»ØÔÚij¸öÄê·ÝyearºÍÔ·Ýmonth·Ö×éϵÄËùÓÐÆÚÊývolumnÐÅÏ¢£¿£¨Ä³¸öÄê·Ý+Ô·ÝϵÄÆÚÊýÐÅÏ¢ÊDz»¹Ì¶¨µÄ£¬Ö»ÄÜͨ¹ýÊý¾Ý¿â²éѯ²ÅÄÜ»ñµÃ£©
¸ÃÊÇGROUP_CONCATÉÏÕóµÄʱºòÁË¡£
Sql´úÂë
selec
Ïà¹ØÎĵµ£º
1¡¢MYSQLµÄ°²×°
1£©Ê×Ïȵ±È»ÊÇÏÂÔØÔ´Âë°ü¿©£¬È磺mysql-5.0.27µÄ°²×°£¬http://www.mysql.com
2£©½âѹËõ£¬>tar -zxvf mysql-5.0.27.tar.gz
3£©½âѹÍê³É£¬½øÈëmysql-5.0.27Îļþ¼Ð£¬>cd mysql-5.0.27
4£©°²×°ÅäÖã¬>./configure --prefix=/opt/mysql --with-extra-charsets=all --with-unix-socket-path=/opt/m ......
1)
°²×°Apache/2.2.14 ºÍ PHP/5.2.12 Ö®ºó£¬ÖØÐÂÆô¶¯Apache£¬Æô¶¯Ê§°Ü¡£²éÁËһЩ×ÊÁÏ£¬»ù±¾Éϸø³öµÄ½¨ÒéÊǽûµôExtension¡£ÎÒ¾Í×°ÁËÒ»¸öExtesion Mysqli¡£
ÐÞ¸Äphp.ini ×¢Ê͵ôÕâÒ»ÐУ¬¹ûÈ»ÉúЧ¡£
[PHP_MYSQLI]
;extension=php_mysqli.dll
°²×°Íêmysql£¬ÔÙÖØÐÂenableÕâ¸öÅäÖþͺÃÁË£¬¹À¼ÆÊÇÒÀÀµÄ³Ð©mysqlµÄ¿â¡£
......
µÚÒ»ÕС¢mysql·þÎñµÄÆô¶¯ºÍÍ£Ö¹
net stop mysql
net start mysql
µÚ¶þÕС¢µÇ½mysql
Óï·¨ÈçÏ£º mysql -uÓû§Ãû -pÓû§ÃÜÂë
¼üÈëÃüÁîmysql -uroot -p£¬ »Ø³µºóÌáʾÄãÊäÈëÃÜÂ룬ÊäÈë12345£¬È»ºó»Ø³µ¼´¿É½øÈëµ½mysqlÖÐÁË£¬mysqlµÄÌáʾ·ûÊÇ£º
mysql>
×¢Ò⣬Èç¹ûÊÇÁ¬½Óµ½ÁíÍâµÄ»úÆ÷ÉÏ£¬ÔòÐèÒª¼ÓÈëÒ»¸ö²ÎÊý-h»úÆ÷IP
µ ......
Ò»¡¢ ·ÖÇøµÄ¸ÅÄî
¶þ¡¢ ΪʲôʹÓ÷ÖÇø£¿£¨Óŵ㣩
Èý¡¢ ·ÖÇøÀàÐÍ
ËÄ¡¢ ×Ó·ÖÇø
Îå¡¢ ¶Ô·ÖÇø½øÐÐÐ޸ģ¨Ôö¼Ó¡¢É¾³ý¡¢·Ö½â¡¢ ......
Ò»¡¢³£ÓÃ×Ö·û´®º¯Êý
1.CONCAT(str1,str2,...)
mysql> ? concat;
mysql> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL ......