MYSQL JOIN SELECT 语句 - 省略重复

Question

我想加入以下 2 个查询，但我有重复的查询......可以删除重复的查询：

(
SELECT bar_id, bar_name, town_name, bar_telephone, 
        (subscription_type_id *2) AS subscription_type_id
FROM bar, sportactivitybar, towns, subscriptiontype
WHERE sport_activity_id_fk =14
    AND bar_id = bar_id_fk
    AND town_id = town_id_fk
    AND subscription_type_id = subscription_type_id_fk
)
UNION 
(
SELECT bar_id, bar_name, town_name, bar_telephone, 
         subscription_type_id
FROM bar, towns, subscriptiontype
WHERE town_id = town_id_fk
    AND subscription_type_id = subscription_type_id_fk
)
ORDER BY subscription_type_id DESC , RAND( )

请注意，我需要省略那些具有较低 subscription_type_id 的重复项

Answer 1

如果我理解正确的话，一个简单的 GROUP BY ，仅保留最大订阅类型就可以解决问题。

SELECT  dupAlias.bar_id
        , dupAlias.bar_name
        , dupAlias.town_name
        , dupAlias.bar_telephone
        , MAX(dupAlias.subscription_type_id) AS subscription_type_id
FROM    (
          SELECT  bar_id
                  , bar_name
                  , town_name
                  , bar_telephone
                  , (subscription_type_id *2) AS subscription_type_id
          FROM    bar
                  , sportactivitybar
                  , towns
                  , subscriptiontype
          WHERE   sport_activity_id_fk =14
                  AND bar_id = bar_id_fk
                  AND town_id = town_id_fk
                  AND subscription_type_id = subscription_type_id_fk
          UNION 
          SELECT  bar_id
                  , bar_name
                  , town_name
                  , bar_telephone
                  , subscription_type_id
          FROM    bar
                  , towns
                  , subscriptiontype
          WHERE   town_id = town_id_fk
                  AND subscription_type_id = subscription_type_id_fk
        ) dupAlias
GROUP BY
        dupAlias.bar_id, dupAlias.bar_name, dupAlias.town_name, dupAlias.bar_telephone
ORDER BY 
        dupAlias.subscription_type_id DESC , RAND( )

Answer 2

您可以将查询括起来：

SELECT bar_id, bar_name, town_name, bar_telephone, 
    max(subscription_type_id)
FROM
(
    (
    SELECT bar_id, bar_name, town_name, bar_telephone, 
            (subscription_type_id *2) AS subscription_type_id
    FROM bar, sportactivitybar, towns, subscriptiontype
    WHERE sport_activity_id_fk =14
        AND bar_id = bar_id_fk
        AND town_id = town_id_fk
        AND subscription_type_id = subscription_type_id_fk
    )
    UNION 
    (
    SELECT bar_id, bar_name, town_name, bar_telephone, 
             subscription_type_id
    FROM bar, towns, subscriptiontype
    WHERE town_id = town_id_fk
        AND subscription_type_id = subscription_type_id_fk
    )
) x
GROUP BY bar_id, bar_name, town_name, bar_telephone
ORDER BY subscription_type_id DESC , RAND( )

Answer 3

最快的方法是使用临时表。从那里，您可以将第一个查询插入到临时表中，然后仅插入第二个查询中不在临时表中的行，方法是使用临时表的外连接或使用 not在声明中。或者，您可以插入所有第二个查询，并在临时表中的选择中使用 group by 子句。