代码之家 › 专栏 › 技术社区 › Paddy Hallihan

SQL-组合查询

sql mysql php

Paddy Hallihan · 技术社区 · 6 年前

我有两个查询可以完全按我想要的方式执行,但是,我想尝试组合这些查询。

查询1:

SELECT DISTINCT categories.id FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.is_sub_cat='1' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

查询2:

SELECT DISTINCT categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.top_cat!='0' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

是否可以将这些组合成1个查询,或者在完成这两个查询之后,我应该只在PHP中组合结果数组?

5 回复 | 直到 6 年前

Fahmi 6 年前

你可以使用 UNION 合并两个查询

SELECT DISTINCT categories.id FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.is_sub_cat='1' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'
UNION 

SELECT DISTINCT categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.top_cat!='0' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

Zaynul Abadin Tuhin 6 年前

你可以在什么时候使用案例

SELECT case when (categories.is_sub_cat='1' AND  categories.is_paused!='1' AND 
        products.nexus='1') then categories.id
        else categories.top_cat end  as id
    FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id

Barbaros Özhan 6 年前

您可以将查询组合为

SELECT DISTINCT categories.id, categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        ( categories.is_sub_cat='1' OR categories.top_cat!='0' ) AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

或者, UNION ALL 如果列 categories.id 和 categories.top_cat 属于同一数据类型,或者可以强制转换为同一数据类型。

dinyo_donchev 6 年前

是的,工会似乎是合法的选择。对于您的情况来说,这并不重要,因为您是从同一个表中进行选择的,但是作为一个补充说明,您应该知道,在使用union时,通常需要在表中具有相同数量的列(即,如果它们不同的话)。

Gordon Linoff 6 年前

您可以在 WHERE 条款:

SELECT DISTINCT c.id
FROM categories c INNER JOIN
     product_categories pc
     ON pc.category_id = c.id INNER JOIN
     products p
     ON pc.product_id = p.id 
WHERE p.nexus = 1 AND
      c.is_paused <> 1 AND
      (c.is_sub_cat = 1 OR
       c.top_cat <> 0 
      );

注:

表别名使查询更易于写入和读取。
假设列是数字,不要将值与字符串进行比较。尽量避免类型转换问题。