MYSQL 常用语句

●获取指定时间段的每一天

 SELECT DATE_FORMAT(date,'%Y-%m-%d')

●获取当前时间两个月前 00:00:00 的时间

SELECT CAST((CAST(SYSDATE()AS DATE) - INTERVAL 2 MONTH)AS DATETIME)

●格式化时间

select date_format(now(), '%Y-%m-%d 23:59:59')

●根据时间查询当前记录上一条

SELECT id,TIMESTAMPDIFF(SECOND,release_time,'2017-02-07 10:30:08') tb FROM p2p_news WHERE news_type = 1 AND  id != 1 HAVING tb>=0 ORDER BY tb  LIMIT 0,1;

● 根据时间查询当前记录下一条

SELECT id,TIMESTAMPDIFF(SECOND,'2017-02-07 10:30:08',release_time) tb FROM p2p_news WHERE news_type = 1 AND  id != :uid HAVING tb>=0 ORDER BY tb  LIMIT 0,1;

●将多级类目放到同一条展示

手机通讯    手机  苹果

SELECT
SUBSTRING_INDEX(t2.concat_name, ',', 1) AS firstLevelTypeDesc,
SUBSTRING_INDEX(
    SUBSTRING_INDEX(t2.concat_name, ',', 2),
    ',' ,- 1
) AS secondLevelTypeDesc,
SUBSTRING_INDEX(t2.concat_name, ',', - 1) AS thirdLevelTypeDesc
FROM
(
    SELECT
        GROUP_CONCAT(b. NAME) AS concat_name
    FROM
        `t_goods_spu` a
    LEFT JOIN `t_goods_types` b ON (
        b.id = a.first_level_type_id
        OR b.id = a.second_level_type_id
        OR b.id = a.third_level_type_id
    )
    GROUP BY
        a.id
    ORDER BY
        a.id
) t2