连接具有不同列值的行

qnzebej0 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(270)

我在做一个可以使用过滤器的搜索引擎。
我的sql是这样的；

SELECT l.location_id, og.*,f.*
        FROM object_types ot
        LEFT JOIN object_group_types ogt ON ogt.object_type_id = ot.object_type_id
        LEFT JOIN object_groups og ON og.object_group_type_id = ogt.object_group_type_id
        LEFT JOIN object_groups2filters og2f ON og2f.object_group_id = og.object_group_id
        LEFT JOIN filters f ON f.filter_id = og2f.filter_id
        LEFT JOIN locations l ON l.location_id = og.location_id
        WHERE ot.object_type_key = 'TYPE_TENNIS';

现在根据用户过滤输入，我想为它选择正确的位置。但是因为我用左连接来连接所有东西，所以我得到了不同行上的所有过滤器项，如图所示。

所以我想选择一个位置，在这个位置，一个位置id同时有filter\u key filter\u grass和filter\u parking。
如果我使用“and f.filter\u key='filter\u parking'and f.filter\u key='filter\u grass'”，它将不起作用，因为过滤器值位于不同的行上。
有人有线索来选择location\u id有两个filter\u键的位置吗？

sql mysql php phpmyadmin laravel-5

来源：https://stackoverflow.com/questions/52813109/join-rows-with-different-column-values

2条答案

按热度按时间

iq0todco1#

你需要使用 Group By 与 Having 筛选出位置。
尝试：

SELECT l.location_id 
FROM object_types ot
LEFT JOIN object_group_types ogt ON ogt.object_type_id = ot.object_type_id
LEFT JOIN object_groups og ON og.object_group_type_id = ogt.object_group_type_id
LEFT JOIN object_groups2filters og2f ON og2f.object_group_id = og.object_group_id
LEFT JOIN filters f ON f.filter_id = og2f.filter_id
LEFT JOIN locations l ON l.location_id = og.location_id
WHERE ot.object_type_key = 'TYPE_TENNIS'
GROUP BY l.location_id 
HAVING SUM(f.filter_key = 'FILTER_PARKING') AND 
       SUM(f.filter_key = 'FILTER_GRASS')

赞(0）回复(0）举报 2021-06-21

xlpyo6sf2#

您可以使用 in 和条件聚合

SELECT l.location_id 
    FROM object_types ot
    LEFT JOIN object_group_types ogt ON ogt.object_type_id = ot.object_type_id
    LEFT JOIN object_groups og ON og.object_group_type_id = ogt.object_group_type_id
    LEFT JOIN object_groups2filters og2f ON og2f.object_group_id = og.object_group_id
    LEFT JOIN filters f ON f.filter_id = og2f.filter_id
    LEFT JOIN locations l ON l.location_id = og.location_id
    WHERE ot.object_type_key = 'TYPE_TENNIS'
   and f.filter_key in ('FILTER_PARKING','FILTER_GRAS')
    GROUP BY l.location_id 
    HAVING SUM(case when f.filter_key = 'FILTER_PARKING' then 1 else 0 end)>=1 AND 
           SUM(case whenf.filter_key = 'FILTER_GRAS' then 1 else 0 end)>=1

赞(0）回复(0）举报 2021-06-20

我来回答

连接具有不同列值的行

2条答案

相关问题

热门标签

最新问答