MySQL实现Airbnb式价格筛选：获取每日最低价及对应房源数

表结构与数据说明 #

存储租赁房源预计算价格的pricecache表结构如下：

CREATE TABLE `pricecache` (
  `oid` int(11) NOT NULL DEFAULT '0',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `duration` tinyint(3) UNSIGNED NOT NULL,
  `guests` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `child` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `animal` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `price` float NOT NULL DEFAULT '0',
  `stamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

示例数据包含同一房源（oid=1162）不同入住起始日、不同客人数的7天总价（679元），对应每日单价约97元。

原SQL的问题 #

你之前的SQL存在两个核心问题：

• 分组字段ROUND(price/duration, 0)与选中的oid不匹配，分组后无法正确关联房源ID
• 没有先对每个房源的最低每日单价做聚合，导致同一房源的不同价格条目重复计算，产生冗余的xprice

正确的SQL实现 #

要实现类似Airbnb的价格筛选（按每日最低单价分组，统计对应房源数量），需要分两步：

1. 先获取每个房源的最低每日单价（一个房源可能有多个价格组合，取最小的每日单价）
2. 再按每日单价分组，统计每个价格档的房源数量

最终SQL语句 #

-- 第一步：获取每个房源的最低每日单价
WITH min_daily_prices AS (
    SELECT 
        oid,
        ROUND(MIN(price / duration), 0) AS min_daily_price
    FROM pricecache
    WHERE price > 0
    GROUP BY oid
)
-- 第二步：按每日单价分组，统计房源数量
SELECT 
    min_daily_price AS xprice,
    COUNT(DISTINCT oid) AS property_count
FROM min_daily_prices
GROUP BY min_daily_price
ORDER BY xprice ASC;

逻辑说明 #

• 使用CTE（WITH子句）先聚合每个房源的最低每日单价，确保每个房源只保留一个最低价格
• 再对这些最低价格分组，用COUNT(DISTINCT oid)统计每个价格对应的房源数量，避免重复计数
• 最终结果按每日单价升序排列，符合价格筛选的展示需求

扩展：支持价格区间分组（类似Airbnb的价格段） #

如果需要像Airbnb一样按价格区间（如0-50元，51-100元等）展示，可以调整分组逻辑：

WITH min_daily_prices AS (
    SELECT 
        oid,
        ROUND(MIN(price / duration), 0) AS min_daily_price
    FROM pricecache
    WHERE price > 0
    GROUP BY oid
)
SELECT 
    -- 定义价格区间，这里按50元为间隔
    CONCAT(
        FLOOR(min_daily_price / 50) * 50,
        ' - ',
        FLOOR(min_daily_price / 50) * 50 + 49
    ) AS price_range,
    COUNT(DISTINCT oid) AS property_count
FROM min_daily_prices
GROUP BY price_range
ORDER BY FLOOR(min_daily_price / 50) ASC;

内容的提问来源于stack exchange，提问作者maidan