MySQLÖÐgroup_concatº¯Êý
±¾ÎÄͨ¹ýʵÀý½éÉÜÁËMySQLÖеÄgroup_concatº¯ÊýµÄʹÓ÷½·¨£¬±ÈÈçselect group_concat(name) ¡£
MySQLÖÐgroup_concatº¯Êý
ÍêÕûµÄÓï·¨ÈçÏ£º
group_concat([DISTINCT] ÒªÁ¬½ÓµÄ×ֶΠ[Order BY ASC/DESC ÅÅÐò×Ö¶Î] [Separator '·Ö¸ô·û'])
»ù±¾²éѯ
mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)
ÒÔid·Ö×飬°Ñname×ֶεÄÖµ´òÓ¡ÔÚÒ»ÐУ¬¶ººÅ·Ö¸ô(ĬÈÏ)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
ÒÔid·Ö×飬°Ñname×ֶεÄÖµ´òÓ¡ÔÚÒ»ÐУ¬·ÖºÅ·Ö¸ô
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)
ÒÔid·Ö×飬°ÑÈ¥ÈßÓàµÄname×ֶεÄÖµ´òÓ¡ÔÚÒ»ÐУ¬
¶ººÅ·Ö¸ô
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)
ÒÔid·Ö×飬°Ñname×ֶεÄÖµ´òÓ¡ÔÚÒ»ÐУ¬¶ººÅ·Ö¸ô£¬ÒÔnameÅŵ¹Ðò
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)
Ïà¹ØÎĵµ£º
1.±³¾°
֮ǰ´ÓûÓùýhibernate£¬Òò´ËÔÚÍøÉÏËÑÁËÒ»ÏÂhibernate¿ìËÙÈëÃÅ·½ÃæµÄÐÅÏ¢£¬×îºóÎÒ°´ÕÕ¡¶Myeclipse Hibernate ¿ìËÙÈëÃÅ ÖÐÎÄ°æ¡·£¨CSDN£¬°Ù¶ÈÎĿⶼÓУ©Ò»ÎÄ¿ªÊ¼ÁËÎÒµÄhibernateÖ®Âã¨ÎªÏîÄ¿×ö¼¼Êõ×¼±¸£©¡£
ÏÂÃ ......
MySQLµÄ×Ö·û¼¯ÎÊÌâ¡£WindowsÏ¿Éͨ¹ýÐÞ¸Ämy.iniÄÚµÄ
CODE:
# CLIENT SECTION
[mysql]
default-character-set=utf8
#
SERVER SECTION
[mysqld]
default-character-set=utf8
Õâ
Á½¸ö×Ö¶ÎÀ´¸ü¸ÄÊý¾Ý¿â
µÄ
ĬÈÏ×Ö·û¼¯¡£µÚÒ»¸öÊÇ¿Í»§¶ËĬÈϵÄ×Ö·û¼¯£¬µÚ¶þ¸öÊÇ·þÎñÆ÷¶ËĬÈϵÄ×Ö·û¼¯¡£¼ÙÉèÎÒÃÇ°ÑÁ½¸ö¶¼ÉèΪutf8£¬È»ºó ......
ÎÒÓõÄÊÇmysql5.0£¬
ÎÒ³ÌÐòÖÐÓõ½ÁËÒ»¾äsqlÊÇ
SQL code
select count(DISTINCT id) from Policyinfo p where p.filename not in
(select p2.filename from Policyreadinfo p2 where p2.reader='test')
µ«ÊÇÿ´ÎÔÚ³ÌÐòÖÐÿ´ÎÖ´ÐÐÕâ¾äsqlʱÍøÒ³¾ÍÌØÂý£¬Òª10¶àÃ룬¶øÕâ2¸ö±íµÄ¼Ç¼ÐÐÊýÒ²¾Í1000¶à¶øÒÑ£¬ÎÒ°ÑÕâ¾äsqlµ¥¶ÀÄ ......
ÔÚһЩÇé¿öÏ£¬MySQL¿ÉÒÔÖ±½ÓʹÓÃË÷ÒýÀ´Âú×ãÒ»¸ö ORDER BY »ò GROUP BY ×Ó¾ä¶øÎÞÐè×ö¶îÍâµÄÅÅÐò¡£¾¡¹Ü ORDER BY ²»ÊǺÍË÷ÒýµÄ˳Ðò׼ȷƥÅ䣬Ë÷Òý»¹ÊÇ¿ÉÒÔ±»Óõ½£¬Ö»Òª²»ÓõÄË÷Òý²¿·ÖºÍËùÓеĶîÍâµÄ ORDER BY ×Ö¶ÎÔÚ WHERE ×Ó¾äÖж¼±»°üÀ¨ÁË¡£ ¡¡¡¡Ê¹ÓÃË÷ÒýµÄMySQL Order By ¡¡¡¡ÏÂÁеļ¸¸ö²éѯ¶¼»áʹÓÃË÷ÒýÀ´½â¾ö ORDER BY ......
µ±ÓÃshow processlist;¿´µ½ÈçÏÂÐÅϢʱ:
LL | login | NULL |
| 697 | unauthenticated user | 218.26.219.186:26944 | NULL | Connect | NULL | login | NULL & ......