php - #1222 - 使用的 SELECT 语句具有不同的列数 UNION 错误

Question

我花了两天多的时间来修复这个查询，但我找不到我的问题所在。我的问题是最后一个 UNION 导致了一些错误，没有它一切正常。但是在最后一个 UNION 中，我没有与 MYSQL 错误状态不同的列数。这是查询:

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
SELECT DISTINCT 323,
                'export',
                1,
                0,
                subscriberid
FROM
  (SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid
   AND lists.listid IN (34)
   WHERE subscribers.listid IN (34)
     AND ((subscribers.emailaddress LIKE '%a%'))
   UNION SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid
   AND lists.listid IN (37)
   WHERE subscribers.listid IN (37)
     AND ((subscribers.emailaddress LIKE '%kim%'))) AS T1
WHERE subscriberid NOT IN
    (SELECT DISTINCT subscribers.subscriberid AS subscriberid
     FROM email_list_subscribers AS subscribers
     JOIN email_lists AS lists ON lists.listid = subscribers.listid
     AND lists.listid IN (37,
                          34)
     WHERE subscribers.listid IN (37,
                                  34)
       AND ((subscribers.emailaddress LIKE '%a%'
             OR subscribers.emailaddress LIKE '%kim%')))
UNION
SELECT DISTINCT subscribers.subscriberid AS subscriberid
FROM email_list_subscribers AS subscribers
JOIN email_lists AS lists ON lists.listid = subscribers.listid
AND lists.listid IN (33)
WHERE subscribers.listid IN (33)
  AND ((subscribers.emailaddress LIKE '%v%'))

Answer 1

您确实有不同数量的列。最后的 UNION 列数只选择了一个列，其中第一个选择你选择了 5 如下:

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
    SELECT DISTINCT 323,                -- These are five columns
                    'export',
                    1,
                    0,
                    subscriberid
    FROM
    (
       ....
    ) AS T1
    WHERE subscriberid NOT IN (... )
    UNION
    SELECT DISTINCT subscribers.subscriberid AS subscriberid -- This where is the error, only one column selected
    FROM email_list_subscribers AS subscribers
    JOIN email_lists AS lists ON lists.listid = subscribers.listid
    AND lists.listid IN (33)
    WHERE subscribers.listid IN (33)
      AND ((subscribers.emailaddress LIKE '%v%'))

你必须像这样修改它才能让它工作:

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
SELECT DISTINCT 323,                -- These are five columns
                'export',
                1,
                0,
                subscriberid
FROM
(
   SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (34)
   WHERE subscribers.listid IN (34)
     AND ((subscribers.emailaddress LIKE '%a%'))
   UNION SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (37)
   WHERE subscribers.listid IN (37)
     AND ((subscribers.emailaddress LIKE '%kim%'))
) AS T1
WHERE subscriberid NOT IN (SELECT DISTINCT subscribers.subscriberid AS subscriberid
                           FROM email_list_subscribers AS subscribers
                           JOIN email_lists AS lists 
                             ON lists.listid = subscribers.listid  
                            AND lists.listid IN (37, 34)
                           WHERE subscribers.listid IN (37, 34)
                             AND ((subscribers.emailaddress LIKE '%a%'
                                  OR subscribers.emailaddress LIKE '%kim%'))
                          )
UNION
SELECT DISTINCT -- You have to add these in order to make the query works
  323,              
  'export',
  1,
  0,
  subscribers.subscriberid AS subscriberid -- this is only one column you selected
FROM email_list_subscribers AS subscribers
JOIN email_lists AS lists ON lists.listid = subscribers.listid
AND lists.listid IN (33)
WHERE subscribers.listid IN (33)
  AND ((subscribers.emailaddress LIKE '%v%'))

---

更新:您还可以将该查询移动到子查询中，就像这样

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
SELECT DISTINCT 323,                -- These are five columns
                'export',
                1,
                0,
                subscriberid
FROM
(
   SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (34)
   WHERE subscribers.listid IN (34)
     AND ((subscribers.emailaddress LIKE '%a%'))
   UNION SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (37)
   WHERE subscribers.listid IN (37)
     AND ((subscribers.emailaddress LIKE '%kim%'))

  UNION

  SELECT DISTINCT subscribers.subscriberid AS subscriberid -- this is only one column you selected
FROM email_list_subscribers AS subscribers
JOIN email_lists AS lists ON lists.listid = subscribers.listid
AND lists.listid IN (33)
WHERE subscribers.listid IN (33)
  AND ((subscribers.emailaddress LIKE '%v%')) 
) AS T1
WHERE subscriberid NOT IN (SELECT DISTINCT subscribers.subscriberid AS subscriberid
                           FROM email_list_subscribers AS subscribers
                           JOIN email_lists AS lists 
                             ON lists.listid = subscribers.listid  
                            AND lists.listid IN (37, 34)
                           WHERE subscribers.listid IN (37, 34)
                             AND ((subscribers.emailaddress LIKE '%a%'
                                  OR subscribers.emailaddress LIKE '%kim%'))
                          )