[mysql]将两个不同查询合并成一个

标签: MySQL PHP
发布时间: 2017/1/9 21:57:39
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有以下查询︰

    SELECT DISTINCT id, title
    FROM      
    ((SELECT 
        DISTINCT offers.id AS id, offers.title AS title

    FROM offers

    INNER JOIN categories
    ON offers.category=categories.title

    WHERE categories.title="Fashion clothes"

    GROUP BY offers.id
    ORDER BY offers.id)
    UNION ALL
    (SELECT 
        DISTINCT offers.id AS id, offers.title AS title

    FROM offers

    INNER JOIN cities
    ON offers.city=cities.title

    WHERE cities.title="Australia"

    GROUP BY offers.id
    ORDER BY offers.id)) as subquery

我想要从表提供获取行的 category=Fashion 衣服和 city=Australia ,但当我使用联盟它返回的所有行。 我不知道如何使它工作。如果有人可以帮助我将不胜感激。

解决方法 1:

你不必为这一个联盟。刚加入的所有表,这两个条件在你哪里子句︰

SELECT 
DISTINCT offers.id AS id, offers.title AS title

FROM offers
INNER JOIN categories
ON offers.category=categories.title
INNER JOIN cities
ON offers.city=cities.title

WHERE categories.title="Fashion clothes" AND cities.title="Australia"
ORDER BY offers.id

正如 RubahMalam 所指出,你甚至不需要联接,当你正在联接表的标题,所以查询可以简化为︰

SELECT 
DISTINCT offers.id AS id, offers.title AS title

FROM offers

WHERE offers.category="Fashion clothes" AND offers.city="Australia"
ORDER BY offers.id

然而,它大概会最好在所有表中有单独唯一的 id,使用那些参加您的查询,但那是另一个故事。

官方微信
官方QQ群
31647020