mysql - 查询返回特定值

Question

给定一个表:
-property_id(整数)
-以米为单位的距离(整数)
-对应的地方类型(text)

我必须编写一个查询来返回以下内容:
1. property_id
2.最近的医院
3.最近的餐厅或咖啡馆
4. 3公里范围内的餐厅或咖啡馆数量
5. 5公里范围内的餐厅或咖啡馆数量

create table property_poi_distances(
    property_id integer,
    place_type text,
    distance integer
);

insert into property_poi_distances(property_id,place_type,distance)
    values
        (1,'Hospital',100),
        (1,'Hospital',200),
        (1,'Restaurant',1000),
        (1,'Restaurant',2500),
        (1,'Cafe',2000),
        (2,'Hospital',5000),
        (2,'Restaurant',2500),
        (2,'Restaurant',4000),
        (2,'Cafe',1000),
        (3,'Hospital',10000),
        (3,'Restaurant',9000);


select t1.property_id,
    min(t2.distance) as closest_hospital,
    min(t3.distance) as closest_restaurant_cafe,
    count(t4.property_id) as number_restaurants_3km,
    count(t5.property_id) as number_restaurants_5km
from property_poi_distances as t1
         join
     property_poi_distances as t2 on t1.property_id = t2.property_id
         join
     property_poi_distances as t3 on t1.property_id = t3.property_id
         join
     property_poi_distances as t4 on t1.property_id = t4.property_id
         join
     property_poi_distances as t5 on t1.property_id = t5.property_id

where
    t2.place_type = 'Hospital'
    and (t3.place_type = 'Restaurant' or t3.place_type = 'Cafe')
    and ((t4.place_type = 'Restaurant' or t4.place_type = 'Cafe') and t4.distance<=3000)
    and ((t4.place_type = 'Restaurant' or t4.place_type = 'Cafe') and t4.distance<=5000)

group by t1.property_id;

预期输出:

Answer 1

您可以使用条件聚合来做到这一点:

select property_id,
    min(case when place_type  = 'Hospital' then distance end) as closest_hospital,
    min(case when place_type in ('Restaurant', 'Cafe') then distance end) as closest_restaurant_cafe,
    sum(place_type in ('Restaurant', 'Cafe') and distance <= 3000) as number_restaurants_3km,
    sum(place_type in ('Restaurant', 'Cafe') and distance <= 5000) as number_restaurants_5km
from property_poi_distances
group by property_id;

参见 demo .
结果

| property_id | closest_hospital | closest_restaurant_cafe   | number_restaurants_3km | number_restaurants_5km |
| ----------- | ---------------- | ------------------------- | ---------------------- | ---------------------- | 
| 1           | 100              | 1000                      | 3                      | 3                      |
| 2           | 5000             | 1000                      | 2                      | 3                      |
| 3           | 10000            | 9000                      | 0                      | 0                      |