【MYSQL】select from group by

標籤：nbsp   use   row   color   from   code   set   strong   sel   

• group by

mysql> select * from t_user group by lvl;+-----+-------+----------+------+--------+| uid | uname | tel      | lvl  | salart |+-----+-------+----------+------+--------+|   1 | alex  | 23131231 | A    |   1100 ||   3 | kit   | 87416574 | B    |   1200 ||   4 | poker | 41874521 | C    |   1560 |+-----+-------+----------+------+--------+mysql> select lvl from t_user group by lvl;+------+| lvl  |+------+| A    || B    || C    |+------+

• group by + group_concat()

mysql> select lvl,group_concat(salart) from t_user group by lvl;+------+----------------------+| lvl  | group_concat(salart) |+------+----------------------+| A    | 1100,4300            || B    | 1200                 || C    | 1560                 |+------+----------------------+3 rows in set (0.00 sec)mysql> select lvl,group_concat(uid) as salary from t_user group by lvl;+------+--------+| lvl  | salary |+------+--------+| A    | 1,2    || B    | 3      || C    | 4      |+------+--------+3 rows in set (0.00 sec)mysql> select lvl,group_concat(salart) from t_user group by lvl;+------+----------------------+| lvl  | group_concat(salart) |+------+----------------------+| A    | 1100,4300            || B    | 1200                 || C    | 1560                 |+------+----------------------+3 rows in set (0.00 sec)mysql> select lvl,group_concat(salart) as salary from t_user group by lvl;+------+-----------+| lvl  | salary    |+------+-----------+| A    | 1100,4300 || B    | 1200      || C    | 1560      |+------+-----------+3 rows in set (0.00 sec)

• #group by + 集合函數

mysql> select lvl,group_concat(uid) as salary from t_user group by lvl;+------+--------+| lvl  | salary |+------+--------+| A    | 1,2    || B    | 3      || C    | 4      |+------+--------+3 rows in set (0.00 sec)mysql> select lvl,count(uid) as salary from t_user group by lvl;+------+--------+| lvl  | salary |+------+--------+| A    |      2 || B    |      1 || C    |      1 |+------+--------+3 rows in set (0.00 sec)

• #group by + having

mysql> select lvl,group_concat(salart) from t_user group by lvl having group_concat(salart)=1200;+------+----------------------+| lvl  | group_concat(salart) |+------+----------------------+| B    | 1200                 |+------+----------------------+1 row in set (0.00 sec)mysql> select lvl,group_concat(salart) from t_user group by lvl having group_concat(salart)=1100;+------+----------------------+| lvl  | group_concat(salart) |+------+----------------------+| A    | 1100,4300            |+------+----------------------+1 row in set (0.00 sec)mysql> mysql> select lvl,count(uid) as salary from t_user group by lvl;+------+--------+| lvl  | salary |+------+--------+| A    |      2 || B    |      1 || C    |      1 |+------+--------+3 rows in set (0.00 sec)mysql> select lvl,count(uid) as salary from t_user group by lvl having count(uid)>1;+------+--------+| lvl  | salary |+------+--------+| A    |      2 |+------+--------+1 row in set (0.00 sec)

 

【MYSQL】select from group by