mysql不常用语句

mysql指定数据排前面

SELECT
    nutr_scheme.id,
    nutr_scheme. NAME,
    nutr_scheme.is_open,
    IFNULL(nutr_scheme_group. NAME, "") AS `group`,
    nutr_scheme.pic,
    nutr_scheme.is_user_diy,
    nutr_scheme.`describe`,
    nutr_user_scheme.relation_id,

IF (
    nutr_drive_history.count,
    nutr_drive_history.count,
    0
) AS drink_count
FROM
    nutr_scheme
LEFT JOIN nutr_user_scheme ON nutr_scheme.id = scheme_id
LEFT JOIN nutr_basedata nutr_scheme_group ON nutr_scheme.group_id = nutr_scheme_group.id
LEFT JOIN (
    SELECT
        nutr_drive_history.scheme_id,
        nutr_drive_history.drive_user,
        COUNT(*) count
    FROM
        nutr_drive_history
    WHERE
        drive_user = 10036
    GROUP BY
        nutr_drive_history.scheme_id
) nutr_drive_history ON nutr_drive_history.scheme_id = nutr_scheme.id
WHERE
    nutr_scheme.is_delete = 0
AND nutr_scheme.is_active = 1
AND (
    (
        nutr_scheme.is_open = 1
        AND nutr_scheme.is_user_diy = 0
    )
    OR (
        nutr_user_scheme.is_delete = 0
        AND nutr_user_scheme.is_active = 1
        AND nutr_user_scheme.user_id = 10036
    )
)
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

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

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

DISTINCT用于去掉count重复值

COUNT(DISTINCT id) AS `count`

查询两分钟以内数据

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

时间戳(10位)转时间

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

一对多关系查询数量

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