IN()子句中的SQL查询

Question

我有一个基于IN()子句的SELECT查询，我想用其他查询来提供该子句，比如：

SELECT *
FROM item_list
WHERE itemNAME
IN (
    SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '17'
    AND    (itemSUB ='1' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1,
    SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '57'
    AND    (itemSUB ='0' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1
)

但它的错误在于：#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT itemNAME FROM item_list WHERE itemID = '57' AND (itemSUB ='0' OR' at line 11

Answer 1

您要查找的语法是union all而不是limit:

SELECT *
FROM item_list
WHERE itemNAME
IN (
    SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '17'
    AND    (itemSUB ='1' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1 union all
    SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '57'
    AND    (itemSUB ='0' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1
)

然而，这可能不会起作用，因为一些SQL引擎（尤其是MySQL）不允许在此类子查询中使用limit。相反，你可以加入：

SELECT il.*
FROM item_list il join
     (select *
      from ((SELECT itemNAME
             FROM   item_list
             WHERE  itemID = '17' AND (itemSUB ='1' OR itemSUB ='0')
             ORDER  BY itemSUB DESC
             LIMIT  1
            ) union
            (SELECT itemNAME
             FROM   item_list
             WHERE  itemID = '57' AND (itemSUB ='0' OR itemSUB ='0')
             ORDER  BY itemSUB DESC
             LIMIT  1
            )
           ) l
     ) l
     on il.itemName = l.itemName;

Answer 2

这里有另一种方法。

SELECT *
FROM item_list
WHERE itemNAME
IN (
SELECT itemNAME
FROM   item_list
WHERE  
(itemID = '17'
AND    (itemSUB ='1' OR itemSUB ='0')
 )
OR
(
itemID = '57' AND    (itemSUB ='0' OR itemSUB ='0')
)
)
ORDER  BY itemSUB DESC
LIMIT  1

但是，除非您正在练习子查询，否则不需要子查询。你只需要这个：

SELECT *
FROM   item_list
WHERE  
(itemID = '17'
AND    (itemSUB ='1' OR itemSUB ='0')
 )
OR
(
itemID = '57' AND    (itemSUB ='0' OR itemSUB ='0')
)
ORDER  BY itemSUB DESC
LIMIT  1

Answer 3

用户Goat CO删除了好答案：

SELECT *
FROM item_list
WHERE itemNAME
= ( SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '17'
    AND    (itemSUB ='1' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1)
OR itemName 
= ( SELECT itemNAME
    FROM   item_list
    WHERE  itemID = '57'
    AND    (itemSUB ='0' OR itemSUB ='0')
    ORDER  BY itemSUB DESC
    LIMIT  1
)