mysql 高级查询

1. 分组字段由多行合并为一行。

mysql> select user_id,tag_id from yyfax_user.user_tag where user_id='U000412280';
+------------+----------+
| user_id    | tag_id   |
+------------+----------+
| U000412280 | TAG20000 |
| U000412280 | TAG20002 |
| U000412280 | TAG50001 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> select user_id,GROUP_CONCAT(tag_id SEPARATOR ',') AS tag_id from yyfax_user.user_tag where user_id='U000412280';
+------------+----------------------------+
| user_id    | tag_id                     |
+------------+----------------------------+
| U000412280 | TAG20000,TAG20002,TAG50001 |
+------------+----------------------------+
1 row in set (0.00 sec)

 

2. 将查询结果按照字段指定的顺序排序。

mysql> select user_id,create_time from yyfax_user.user_info where user_id in ('400','600','100');
+---------+---------------------+
| user_id | create_time         |
+---------+---------------------+
| 100     | 2014-10-08 08:58:58 |
| 400     | 2014-10-09 15:34:02 |
| 600     | 2014-10-10 11:26:10 |
+---------+---------------------+
3 rows in set (0.00 sec)

mysql> select user_id,create_time from yyfax_user.user_info where user_id in ('400','600','100') order by field (user_id,'400','600','100');
+---------+---------------------+
| user_id | create_time         |
+---------+---------------------+
| 400     | 2014-10-09 15:34:02 |
| 600     | 2014-10-10 11:26:10 |
| 100     | 2014-10-08 08:58:58 |
+---------+---------------------+
3 rows in set (0.01 sec)

 

3. 一张表的两个字段对应另一张表的同一字段。

select * from yyfax_user.user_info a 
left join yyfax_user.authentication_info b on a.user_id=b.user_id
left join yyfax_user.authentication_info c on a.id=c.user_id；