我花了两天多的时间修复这个查询,但我找不到我的问题在哪里。我的问题是最后一个 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%'))
你确实有不同的列数。列的最后UNION
个仅选择一列,其中第一个选择
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%'))
)