每天一个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.
只要开始，虽晚不迟。
