MySQL不常用语句

1 指定数据排前面

GROUP BY nutr_scheme.id ORDER BY nutr_scheme.id NOT IN (SELECT scheme_id FROM nutr_user_default_scheme where user_id = 10036 AND is_delete = 0), drink_count DESC, nutr_scheme.id ASC

2 多行查询结果合并一行(适用于一对多)

SELECT
    GROUP_CONCAT(NAME) AS ALL_NAME,
    `name`
FROM
    base_basedata
GROUP BY
    pid

3 DISTINCT用于去掉count重复值

COUNT(DISTINCT id) AS `count`

4 查询两分钟以内数据

create_time >= UNIX_TIMESTAMP(now()-interval 120 second)

5 时间戳(10位)转时间

FROM_UNIXTIME(time_stamp, '%Y-%m-%d %H:%i:%S')

6 一对多查询数量

SELECT `user`.id
           , `user`.`name`
           , user_score.count
FROM `user`
LEFT JOIN (SELECT user_id
                              , COUNT(*) count
                   FROM user_score
                   GROUP BY user_id) AS user_score ON user_score.user_id = `user`.id  

主表 user   子表  user_score   
关系    user_score.user_id = `user`.id