mysql分组取第一个值

最新推荐文章于 2024-07-28 02:47:35 发布

转载最新推荐文章于 2024-07-28 02:47:35 发布 · 783 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/cafebaber/p/6344272.html

文章标签：

#数据库

业务需要，统计交易数据tps，sql如下：

SELECT
    idate ,
    itime ,
    num ,
    rank
FROM
    (
        SELECT
            tmp.idate ,
            tmp.itime ,
            tmp.num ,

        IF(
            @pdept = tmp.idate ,@rank :=@rank + 1 ,@rank := 1
        ) AS rank ,
        @pdept := tmp.idate
    FROM
        (
            SELECT
                DATE_FORMAT(inserttime , '%Y-%m-%d') idate ,
                inserttime AS itime ,
                count(1) AS num
            FROM
                info_bind
            GROUP BY
                DATE_FORMAT(inserttime , '%Y-%m-%d') ,
                inserttime
            ORDER BY
                idate ASC ,
                num DESC
        ) tmp ,
        (SELECT @pdept := NULL ,@rank := 0) a
    ) result
WHERE
    rank = 1