先排序后分组

每天一个BUG小技巧

需求：
查询指定条件下的楼盘最高均价
思路：

先排序查询指定条件楼盘的均价

SELECT
    iAutoID,iBlockID,sBlockName,sTime,fTrademoney 
FROM
    t_loupan_trade 
WHERE
    sTime = "2019-04-07" 
    AND iBlockID IN ( 27, 115 ) 
ORDER BY
    fTrademoney DESC

结果如下：

image.png

再查询分组后指定条件的楼盘均价

SELECT
    iBlockID,sBlockName,sTime,fTrademoney 
FROM
    (
    SELECT
        iBlockID,sBlockName,sTime,fTrademoney 
    FROM
        t_loupan_trade 
    WHERE
        sTime = "2019-04-07" AND iBlockID IN ( 27, 115 ) 
    ORDER BY 
        fTrademoney DESC
    ) AS t 
GROUP BY
    t.sBlockName
ORDER BY 
        fTrademoney DESC;

你会发现结果并不正确：

image.png

查询文章后发现MySQL版本在5.6是可以的。
MySQL 5.7 需要加 limit 来使用，否则会被优化掉。

SELECT
    iBlockID,sBlockName,sTime,fTrademoney 
FROM
    (
    SELECT
        iBlockID,sBlockName,sTime,fTrademoney 
    FROM
        t_loupan_trade 
    WHERE
        sTime = "2019-04-07" AND iBlockID IN ( 27, 115 ) 
    ORDER BY 
        fTrademoney DESC limit 99999999
    ) AS t 
GROUP BY
    t.sBlockName
ORDER BY 
        fTrademoney DESC;

image.png

better late than never.
只要开始，虽晚不迟。