mysql - 错误 1242 - 子查询返回多于 1 行

Question

这个脚本可能有什么问题？我收到错误

“[错误] 1242 - 子查询返回超过 1 行”。

如何解决这个问题？谢谢!

    SELECT
  j.id AS JobID,
        CASE WHEN j.id = (SELECT o.attachment_id from origins o where o.attachment_type="Job" AND o.alliance_partner_id = 12) then "Channel 1"
        WHEN j.id = (SELECT o.attachment_id from origins o where o.attachment_type="Job" AND o.alliance_partner_id = 15) then "Channel 2"
        WHEN j.id = (SELECT t.record_id FROM tracked_records t where t.record_type = 'Job') then "Channel 3"
        WHEN j.id = (SELECT r.job_id from job_referrers r where r.url IN ('ios','android')) then "Channel 4"
        END AS "Channels"
FROM jobs j
GROUP BY j.id
;

Answer 1

从语法上修复查询的一个选项是对每个子 SELECT 使用 WHERE IN:

SELECT j.id AS JobID,
    CASE WHEN j.id IN (SELECT o.attachment_id from origins o where o.attachment_type="Job" AND o.alliance_partner_id = 12) THEN "Channel 1"
         WHEN j.id IN (SELECT o.attachment_id from origins o where o.attachment_type="Job" AND o.alliance_partner_id = 15) THEN "Channel 2"
         WHEN j.id IN (SELECT t.record_id FROM tracked_records t where t.record_type = 'Job') THEN "Channel 3"
         WHEN j.id IN (SELECT r.job_id from job_referrers r where r.url IN ('ios','android')) THEN "Channel 4"
    END AS "Channels"
FROM jobs j
GROUP BY j.id

请注意，虽然这可能会使查询运行，但从逻辑上讲它可能不是您最初想要的。因此，您应该重新审视您想要在这里实现的目标。