mysql - 具有两个表和多个数据和价格范围的复杂查询

Question

假设我有这些表:

[ properties ]
 id (INT, PK)
 name (VARCHAR)

[ properties_prices ]
 id (INT, PK)
 property_id (INT, FK)
 date_begin (DATE)
 date_end (DATE)
 price_per_day (DECIMAL)
 price_per_week (DECIMAL)
 price_per_month (DECIMAL)

我的访问者运行如下搜索:列出从 5 月 1 日到 12 月 31 日期间每天价格(price_per_day 字段)在 10 到 100 之间的前 10 个(分页)属性

我知道这是一个巨大的查询，我需要对结果进行分页，所以我必须完成所有计算并只在一个查询中登录...这就是我来这里的原因! :)

关于问题的疑问

如果有差距，那是可以接受的属性吗？

没有间隙。所有可能的日期都在数据库中。

如果某些时段的价格在 10 到 100 之间，而在其他时段则不是，您是否想获得该特性？

在完美的世界中，不...我们需要计算该时期内该类型价格的“总和”，考虑所有变化/时期。

此外，“前 10 个”是什么？他们是如何订购的？最低价优先？但价格可能不止一个。

这只是一个分页示例，每页有 10 个结果...可以通过全文搜索进行排序，我将添加关键字和这些东西...正如我所说，这是一个相当大的查询。

Answer 1

这与@mdma 给出的答案类似，但我在价格范围的连接子句中使用了条件，而不是 HAVING 技巧。

SELECT p.id, MAX(p.name), 
  MIN(v.price_per_day) AS price_low,
  MAX(v.price_per_day) AS price_high
FROM properties p
JOIN properties_prices v ON p.id = v.property_id
  AND v.price_per_day BETWEEN 10 AND 100  
  AND v.date_begin < '2010-12-31' AND v.date_end > '2010-05-01'
GROUP BY p.id
ORDER BY ...
LIMIT 10;

我还建议创建覆盖索引:

CREATE INDEX prices_covering ON properties_prices
  (property_id, price_per_day, date_begin, date_end);

这允许您的查询以尽可能最佳的方式运行，因为它可以直接从索引中读取值。它根本不需要从表中读取数据行。

+----+-------------+-------+-------+-----------------+-----------------+---------+-----------+------+--------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref       | rows | Extra                    |
+----+-------------+-------+-------+-----------------+-----------------+---------+-----------+------+--------------------------+
|  1 | SIMPLE      | p     | index | PRIMARY         | PRIMARY         | 4       | NULL      |    1 |                          |
|  1 | SIMPLE      | v     | ref   | prices_covering | prices_covering | 4       | test.p.id |    6 | Using where; Using index |
+----+-------------+-------+-------+-----------------+-----------------+---------+-----------+------+--------------------------+