MySQL从table1中选择x、y、z，其中x( SUBQUERY具有多个结果行)。是工会还是加入还是怎样？

Question

我已经搜索过，但没有找到任何好的和漂亮的解决方案。如下所示，解决方案2、3和4都可以工作。然而，SQL代码又长又丑，而且只有解决方案4是快速的。一定有一个更整洁的方式来编码这个吗？我要的是漂亮的外表。也是快速的查询。

我想做些像这样整洁的事情：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
    WHERE pd.ean IN (SELECT ean, ean2, ean3, ean4, ean5 FROM product_data WHERE ean = 3607343811835')

因为子查询返回多列而不是多行，所以这不起作用。所以我在堆栈溢出上发现，我可以用JOIN或UNION来解决这个问题。

好的，我尝试这些解决方案。第一：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        
    WHERE pd.ean IN (
        
        SELECT ean
        FROM product_data 
        WHERE ean = 3607343811835 AND ean != ''
        
        UNION
        
        SELECT ean2
        FROM product_data 
        WHERE ean = 3607343811835 AND ean2 != ''
        
        UNION
        
        SELECT ean3
        FROM product_data 
        WHERE ean = 3607343811835 AND ean3 != ''
        
        UNION
        
        SELECT ean4
        FROM product_data 
        WHERE ean = 3607343811835 AND ean4 != ''
        
        UNION
        
        SELECT ean5
        FROM product_data 
        WHERE ean = 3607343811835 AND ean5 != ''
        )

尽管子查询只返回3行并以毫秒为单位运行，但上面的整个查询以90秒的时间结束。我不明白为什么，因为下面以毫秒为单位运行(是的，这3个值正是上面解决方案的子查询在毫秒内返回的)：

 SELECT pd.ean, pd.stocklist, pd.stock_is_current
        FROM price_data pd
            
        WHERE pd.ean IN (3607343811835, 0088300107407, 0088300607402)

下面测试这个查询，按照Akina的建议将结构规范化(它有一个只有3行的新表alternative_eans )。子查询只返回3607343811835、0088300107407和0088300607402。这也是非常快。(所以我仍然不明白为什么上面的解决方案1如此缓慢，而且，对于我现在拥有的表结构，难道没有一个很好的查询吗？)

SELECT pd.ean, pd.stocklist, pd.stock_is_current
        FROM price_data pd
        WHERE pd.ean IN (SELECT alternative_eans FROM eans_to_more_eans WHERE main_ean = 3607343811835)

下面的解决方案2和3工作得更好，它们每个运行大约22秒，并给出相同的结果集。

解决方案2

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        
    WHERE (pd.ean = (
        
        SELECT ean
        FROM product_data 
        WHERE ean = 3607343811835 AND ean != ''
        
) OR pd.ean = (
        
        SELECT ean2
        FROM product_data 
        WHERE ean = 3607343811835 AND ean2 != ''
        
) OR pd.ean = (
        
        SELECT ean3
        FROM product_data 
        WHERE ean = 3607343811835 AND ean3 != ''
        
) OR pd.ean = (
        
        SELECT ean4
        FROM product_data 
        WHERE ean = 3607343811835 AND ean4 != ''
        
) OR pd.ean = (
        
        SELECT ean5
        FROM product_data 
        WHERE ean = 3607343811835 AND ean5 != ''
    
))

解决方案3，大约与上面的解决方案2一样快。22秒：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        INNER JOIN product_data prod
            ON (pd.ean = prod.ean
            or pd.ean = prod.ean2
            or pd.ean = prod.ean3
            or pd.ean = prod.ean4
            or pd.ean = prod.ean5)
            AND pd.ean != ''
            
            
    WHERE (prod.ean = 3607343811835 or
                     prod.ean2 = 3607343811835 or
                   prod.ean3 = 3607343811835 or
                   prod.ean4 = 3607343811835 or
                   prod.ean5 = 3607343811835 )

解决方案4，快速，低于100毫秒。但又长又丑的质疑。一定有更漂亮的方法吗？

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean
FROM product_data 
WHERE ean = 3607343811835 AND ean != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean2
FROM product_data 
WHERE ean = 3607343811835 AND ean2 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean3
FROM product_data 
WHERE ean = 3607343811835 AND ean3 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean4
FROM product_data 
WHERE ean = 3607343811835 AND ean4 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean5
FROM product_data 
WHERE ean = 3607343811835 AND ean5 != ''
)

它们都返回相同的结果集(但顺序不同，但这并不重要)

Answer 1

以上都不是。

问题是不应该在列之间喷洒数组。也就是说，ean, ean2, ean3, ean4, ean5不应该在5列中，而应该在单独的表中的5行中。然后它变成了一个简单的JOIN --没有UNION，没有子查询，等等。

此外，它允许有超过或少于5个"eans“。

参见“1-多”映射和“多到多”映射。

UNION执行每个组件SELECTs，将它们写入临时表。然后它就不复制那张桌子了。最后，将生成的临时表传递给调用方。更改为UNION ALL可能会避免临时表和脱扣器。但还是会很慢的。

OR使优化器抛出它的手并以蛮力执行查询--即扫描整个表，检查每一行而不使用任何INDEX。一个例外是当它可以转换为IN(..)时，这在您的示例中是不可能发生的。