msyql 分组查询

最新推荐文章于 2024-09-14 23:54:55 发布

三思再三思

最新推荐文章于 2024-09-14 23:54:55 发布

阅读量373

点赞数

分类专栏： mysql 文章标签： sql

本文链接：https://blog.csdn.net/ZuoZhiHen/article/details/78929713

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

目的：求一定时间内的每分钟（按分钟分组），每分钟数据取5个最大的值。然后把他们求和列出
1、按时间分组，每分钟取5个最大值

SELECT
    a.*
FROM
    device_raw_data_voice a
WHERE
    substr(activeTime, 1, 16) BETWEEN '2017-12-14 14:53'
AND '2017-12-14 15:55'
AND EXISTS (
    SELECT
        count(*)
    FROM
        device_raw_data_voice
    WHERE
        substr(activeTime, 1, 16) BETWEEN '2017-12-14 14:53'
    AND '2017-12-14 15:55'
    AND substr(activeTime, 1, 16) = substr(a.activeTime, 1, 16)
    AND wave > a.wave
    HAVING
        Count(*) < 5
)
ORDER BY
    a.wave DESC

select a.* from device_raw_data_voice a //明细数据
where substr(activeTime,1,16) between '2017-12-14 14:53' and '2017-12-14 15:53' //区间范围写两遍是为了减少数据量，运行的能快点
and exists ( //这里边是判断a表中的当前条内的wave。比它大的是不是有5个以内条数。比如说这条里的wave在这一分钟内只有一条比它大的就说明它是第二大的
select count(*) //count(*) 就是统计有多少条比它大的
from device_raw_data_voice where substr(activeTime,1,16) between '2017-12-14 14:53' and '2017-12-14 15:53' //为了减少数据量，加快运行速度
and substr(activeTime,1,16) = substr(a.activeTime,1,16) and wave > a.wave //因为要计算比它大的有多少条 所以是>
having Count(*) < 5 //5 就是 0 1 2 3 4 都满足条件 共5条
) order by a.wave desc

2、求出一定时间内每分钟5个最大值的和

SELECT
    substr(a.activeTime, 1, 16),
    sum(a.wave)
FROM
    device_raw_data_voice a
WHERE
    substr(a.activeTime, 1, 16) BETWEEN '2017-12-14 14:53'
AND '2017-12-14 15:53'
AND EXISTS (
    SELECT
        count(*)
    FROM
        device_raw_data_voice
    WHERE
        substr(activeTime, 1, 16) BETWEEN '2017-12-14 14:53'
    AND '2017-12-14 15:53'
    AND substr(activeTime, 1, 16) = substr(a.activeTime, 1, 16)
    AND wave > a.wave
    HAVING
        Count(*) < 5
)
GROUP BY
    substr(a.activeTime, 1, 16)