php - 修复骇客的SQL where子句

我有两个表，activities和activity_tours。 activities hasMany activity_tours并由activities.id> activity_tours.activity_id关联。我已经编写了以下SQL，以在两个表中均具有两个条件的情况下选择行。


如果它具有某个activity_tours.tour_type或
该活动有多个游览


该查询似乎有点骇人听闻。有人可以帮助我提高此查询的效率。

SELECT SQL_CALC_FOUND_ROWS
    activities.activity_name,
    activities.image_path,
    activity_tours.tour_type
FROM
    activities
    LEFT JOIN activity_tours ON activities.id = activity_tours.activity_id
WHERE
    (
        activity_tours.tour_type != 17 OR (
            (SELECT count(*) FROM activity_tours WHERE activity_id = activities.id) > 1)
    )

最佳答案

SELECT a.activity_name,
    a.image_path,
    at.tour_type,
    MAX(at.tour_type != 17) AS tour_not_17,
    COUNT(*) AS tour_count
FROM activities a
INNER JOIN activity_tours at ON a.id = at.activity_id
GROUP BY a.activity_name, a.image_path
HAVING tour_not_17 OR tour_count > 1

不知道如何处理tour_type列；应该使用聚合，但是我不知道是哪一个。也许您可以做GROUP_CONCAT()吗？

关于php - 修复骇客的SQL where子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11331346/