MySQL查询-复杂计数条件 [英] MySQL query - complex count 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为

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 is car (value - 1), bike (value - 2), football (value - 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，即，一个属性具有海滩和山脉，汽车和别墅以及房屋: /p>

I'm using the following 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:

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 

假设我得到的计数(具有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， amenity_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.

但是使用上面的查询，我无法获得以下条件的计数:

But I'm not able to get the count for the following conditions using the above query:

1. (location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的属性ID)和location_type = 3

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

(具有location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的属性ID)和location_type = 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 location_type = 4

的计数(具有location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的property_id)和amenity_type = 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 amenity_type = 2

的计数(具有location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1的property_id)和amenity_type = 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 = 3

是否有任何有效的方法来获取具有不同location_type，amenity_type等的计数.

Is there any efficient way to get the count with different location_type, amenity_type, etc.

请参考我之前的问题- MySQL查询-复杂搜索条件

Please refer to my earlier question - MySQL query - complex searching condition

推荐答案

您可以使用基本查询来获取所有property.id(并对它们进行计数)和SELECT列表中的子查询以计算其他条件，例如:

You can use your base query to get all property.id (and count them) and subqueries in the SELECT list to count the additional conditions, like this:

SELECT COUNT(*) AS BaseCount
     , ( SELECT COUNT(*)
         FROM location AS l3
         WHERE l3.property_id = p.id  
           AND l3.location_type = 3
       ) AS CountLocation3
     , ( SELECT COUNT(*)
         FROM location AS l4
         WHERE l4.property_id = p.id  
           AND l4.location_type = 4
       ) AS CountLocation4
     , ( SELECT COUNT(*)
         FROM amenities AS a2
         WHERE a2.property_id = p.id  
           AND a2.amenity_type = 2
       ) AS CountAmenity4
     , ...
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 = 1                 
  JOIN
    properties AS p3
        ON  p3.property_id = p.id  
        AND p3.property_type = 3 
  JOIN
    properties AS p1 
        ON  p1.property_id = p.id  
        AND p1.property_type = 1 

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