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_countFROM    nutr_schemeLEFT JOIN nutr_user_scheme ON nutr_scheme.id = scheme_idLEFT JOIN nutr_basedata nutr_scheme_group ON nutr_scheme.group_id = nutr_scheme_group.idLEFT 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.idWHERE    nutr_scheme.is_delete = 0AND nutr_scheme.is_active = 1AND (    (        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.idORDER BYnutr_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_basedataGROUP BY    pid

DISTINCT用于去掉count重复值

COUNT(DISTINCT id) AS `count`

查询两分钟以内数据

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

时间戳转时间

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