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

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 |
+----+-------------+-------+-------+-----------------+-----------------+---------+-----------+------+--------------------------+

Answer 2

你告诉我们的不够精确。根据您的数据结构和您的问题，我假设：

• 该时期内房产的价格可能会发生变化，并且每个子时期都会有一个properties_price条目，
• 子时期中不应该有重叠，但数据结构不会保证
• 子期间可以有间隙，

但仍然存在问题：

• 如果有间隙，这是否是可接受的属性？
• 如果在某些超级时期价格在 10 到 100 之间，但在其他时期不是，您想购买该房产吗？
• 另外，“前10个”是什么？它们是如何订购的？最低价优先？但价格可能不止一种。

根据答案，可能没有单个查询可以解决问题。但是，如果您接受差距，则可能会返回您想要的结果：

SELECT *
FROM properties AS p
WHERE EXISTS          -- property is available in the price range
     (SELECT * FROM properties_prices AS pp1 
      WHERE p.id = pp1.property_id AND
            pp1.price_per_day between 10 and 100 AND
            (pp1.date_begin <= "2010-12-31" OR pp1.date_end >= "2010-05-01")) AND
      NOT EXISTS      -- property is in the price range in all sup-periods, but there might be gaps
     (SELECT * FROM properties_prices AS pp2 
      WHERE p.id = pp2.property_id AND
            pp2.price_per_day not between 10 and 100 AND
            (pp2.date_begin <= "2010-12-31" OR pp2.date_end >= "2010-05-01"))
ORDER BY name  --- ???
LIMIT 10  

该查询不会为您提供价格或其他详细信息。这需要进行额外的查询。但也许我的假设都是错误的。

Answer 3

这也可以作为 GROUP BY 来完成，我认为这会非常有效，并且我们得到一些聚合作为包的一部分：（

SELECT 
   prperty_id, MIN(price_per_day), MAX(price_per_day)
FROM 
   properties_prices 
WHERE 
   date_begin <= "2010-12-31" AND date_end >= "2010-05-01"
GROUP BY 
   property_id
HAVING MIN(IF( (price_per_day BETWEEN 10 AND 100), 1, 0))=1
ORDER BY ...
LIMIT 10

我手头没有 MySQL，所以我没有测试过。我不确定MIN(IF ...) 但使用 CASE 的模型在 SQLServer 上运行。）