MySQL查询-复杂的搜索条件 [英] MySQL query - complex searching condition

问题描述

我有一个表"location"，其结构为:

I've a table "location" with the structure:

id  | property_id | location_type
1   | 1           | 1
2   | 1           | 2
3   | 2           | 1
4   | 3           | 2
5   | 4           | 1
6   | 4           | 2

我还有另一个表"amenities"，其结构为:

I've another table "amenities" with the structure:

id  | property_id | amenity_type
1   | 1           | 1
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 1
6   | 4           | 3

我还有另一个表"property"，其结构为:

I've another table "property" with the structure:

id  | property_id | property_type
1   | 1           | 2
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 2
6   | 4           | 3

id-是相应表的主键. property_id是我的数据库的属性ID(外键). location_type是海滩(值-1)，山地(值-2).

id - is the primary key of the respective table. property_id is the property ID of my database (foreign key). location_type is beach (value - 1), mountain (value - 2).

amenity_type是汽车(值-1)，自行车(值-2)，足球(值-3).

amenity_type is car (value - 1), bike (value - 2), football (value - 3).

property_type是别墅(值-2)，房屋(值-3)

property_type is villa (value - 2), house (value - 3)

能否请您帮我获取SQL查询，以选择location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的property_id，即，某个属性具有沙滩，山脉，汽车，别墅和房屋

Can you please help me in getting the SQL query to select the property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1 i.e. a property has beach and mountains and car and villa and house.

这只是我的属性搜索应用程序中过滤器的示例.可以有n种组合.请分享一个将所有这些表连接在一起的通用逻辑，并对其进行优化以搜索大约一百万条记录.

This is just an example of a filter in my property search application. There can be n combinations for this. Please share a common logic which will join all these tables and be optimized to search for around a million records.

我还需要计算所有条件.请共享相同的查询.

I also need count for all the conditions. Please share query for the same.

:

假设我得到的计数(具有location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的property_id)为1500.我需要获得具有相同条件以及其他property_type，location_type，设施类型.

suppose I get the count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) as 1500. I need to get the count with same condition and other property_type, location_type, amenity_type.

例如:

1)(location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1)AND location_type = 3的(property_id)的计数

1) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 3

2)(location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1)AND location_type = 4的(property_id)的计数

2) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 4

3)(location_type = 1 AND location_type = 2 AND amenity_type = 1的(property_id)的计数 AND property_type = 3 AND property_type = 1)AND amenity_type = 2

3) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 2

4)(location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1)AND amenity_type = 3的(property_id)的计数

4) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 3

，依此类推.这对我来说是一笔很大的开销.请帮忙.另外，请注意location_types，amenity_type，property_type是动态的，即用户可以在主表中添加更多location_type，而我需要获取更多location_type的计数.

and so on. Its getting a big overhead for me. Please help. Also, please note that location_types, amenity_type, property_type are dynamic i.e. a user can add more location_type in master tables and I need to get the count for any more location_types.

推荐答案

在具有多个值的情况下，多个表没有问题.你在这里做什么都很好.这是您需要的查询:

there's nothing wrong with multiple tables in a case like this where you have multiple values. what you are doing here is fine. here's the query you need:

select distinct l1.property_id                                                       
  from location as l1, location as l2,                                      
       amentities as a,                                                     
       properties as p1, properties as p2                                   
 where l1.property_id = l2.property_id                                      
   and l1.property_id = a.property_id                                       
   and l1.property_id = p1.property_id                                      
   and l1.property_id = p2.property_id                                      
   and l1.location_type = 1                                                 
   and l2.location_type = 2                                                 
   and a.amenity_type = 1                                                   
   and p1.property_type = 3                                                 
   and p2.property_type = 1           

一旦您知道如何写，就很容易写

it's easy to write once you see how:

1. 为所需的每个表/条件组合创建别名
2. 确保所有地址都同时具有相同的property_id(l1.property_id = ...)
3. 然后为每个表/条件指定条件

您也可以显式使用"join"，但是我发现上面的方法更简单，对数据库引擎也没有关系.

you could also use "join" explicitly, but i find the approach above simpler and it shouldn't matter to the db engine.

[从ypercube编辑，显示JOIN语法]:

:

SELECT p.id  
FROM 
    property AS p           
  JOIN
    location AS l1
        ON  l1.property_id = p.id  
        AND l1.location_type = 1 
  JOIN
    location AS l2
        ON  l2.property_id = p.id  
        AND l2.location_type = 2 
  JOIN                      
    amentities AS a1
        ON  a1.property_id = p.id
        AND a1.amenity_type = 2                 
  JOIN
    properties AS p1
        ON  p1.property_id = p.id  
        AND p1.property_type = 3 
  JOIN
    properties AS p2 
        ON  p2.property_id = p.id  
        AND p2.property_type = 1 

[来自ac的评论:此语法和初始语法应在内部翻译成相同的查询，因此两者的效率均相同]

[comment from ac: this and the initial syntax should be translated internally into the same query, so both are equally efficient]

[关于性能的编辑]通常，为获得良好的数据库性能而需要担心的唯一(或至少到目前为止，最重要的)事情就是索引.您想在每个表的property_id列以及所拥有的不同类型的列上声明索引.这很关键.但是一旦有了，只有几百万行，这应该很快-上面的不是一个非常复杂的查询，并且您的数据少于GB(考虑对类型列使用tinyint ).不用担心...而且别名("as X")根本不是问题.

[edit about performance] in general, the only (or at least, by far the most important) thing you need to worry about for good database performance is indices. you want to declare an index on the property_id column of every table, and also on the different type columns you have. that is critical. but once you have that, for just a few million rows, this should be fast - the above is not a very complex query and you have less than a GB of data (consider using tinyint for the type columns). don't worry... and the aliases (the "as X") are not an issue at all.

[c0>的计数编辑]，您需要以下内容:

[edit for counts] for count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = X you want something like:

select lx.location_id, count(l1.property_id)
  from location as l1, location as l2, location as lx
       amentities as a,
       properties as p1, properties as p2
 where l1.property_id = l2.property_id
   and l1.property_id = a.property_id
   and l1.property_id = p1.property_id
   and l1.property_id = p2.property_id
   and l1.property_id = lx.property_id
   and l1.location_type = 1
   and l2.location_type = 2
   and a.amenity_type = 1
   and p1.property_type = 3
   and p2.property_type = 1
 group by lx.location_type

但是我还没有测试过.应该会给您多行，并带有location_type和每行的计数(因此，您可以一次完成上面给出的所有查询).

but i haven't tested it or anything. that should give you multiple rows, with the location_type and the count for each row (so you do all the queries you gave above in one).

这篇关于MySQL查询-复杂的搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！