在 IF 條件中使用子查詢

你好我有一個奇怪的問題目前我有以下查詢

SELECT *
FROM `txt_campaign_message` `cm`
        LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
        LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
        LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
 AND (NOT (`cs`.`subscriber_id` IS NULL))
 AND cs.subscriber_id = (
   (IF
       (cm.date_range_from IS NOT NULL,
        (SELECT cs1.subscriber_id
          from `txt_campaign_subscriber` cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
        ),
        (cs.subscriber_id)
       )
   )
 )
 AND (`cm`.`campaign_id` = 1386)

這給了我一個錯誤

子查詢返回多行

我將查詢部分更改AND cs.subscriber_id = (IF為使用IN，而不是=在下面的條件之前

SELECT *
FROM `txt_campaign_message` `cm`
        LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
        LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
        LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
 AND (NOT (`cs`.`subscriber_id` IS NULL))
 AND cs.subscriber_id IN (
   (IF
       (cm.date_range_from IS NOT NULL,
        (SELECT cs1.subscriber_id
          from `txt_campaign_subscriber` cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
        ),
        (cs.subscriber_id)
       )
   )
 )
 AND (`cm`.`campaign_id` = 1386)

但是錯誤仍然是一樣的，令我驚訝的是，如果我刪除了 if 條件並只使用子查詢一切執行良好

SELECT *
FROM `txt_campaign_message` `cm`
        LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
        LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
        LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
 AND (NOT (`cs`.`subscriber_id` IS NULL))
 AND cs.subscriber_id IN (
        (SELECT cs1.subscriber_id
          from `txt_campaign_subscriber` cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
        )
 )
 AND (`cm`.`campaign_id` = 1386)

當我重新添加條件時，它開始拋出相同的錯誤，我做錯了什麼或者如果我將 IF 條件用於子查詢，我需要更改什麼，我已經更改為=，IN因為我需要多行進行比較。

我正在使用MariaDB (10.3)我面臨問題的地方。

問題是 IF 語句不能包含集合，它必須是標量。讓我們來說明：

create table T (x int not null);
insert into T (x) values (1),(2);
-- invalid since `SELECT x FROM T` is a set of rows
SELECT * FROM T WHERE 1 IN ( IF (1=1, SELECT x FROM T, 1) );

小提琴

讓我們看看您的查詢是否可以重新表述（我稍微改變了它以使其更具可讀性）：

SELECT *
FROM txt_campaign_message cm
LEFT JOIN txt_campaign c 
   ON c.id = cm.campaign_id
LEFT JOIN txt_message m 
   ON m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs 
   ON cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2 
 AND m.message_type = blast 
 AND c.active = 1
 -- AND cs.subscriber_id IS NOT NULL, redundant and can be removed 
 AND cs.subscriber_id IN (
   IF (cm.date_range_from IS NOT NULL
      , (SELECT cs1.subscriber_id
         FROM txt_campaign_subscriber cs1
         WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) 
                                       AND DATE(cm.date_range_to)
        )
      , cs.subscriber_id
      )
 )
 AND cm.campaign_id = 1386;

這是有問題的部分：

AND cs.subscriber_id IN (
   IF (cm.date_range_from IS NOT NULL
      , (SELECT cs1.subscriber_id
         FROM txt_campaign_subscriber cs1
         WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) 
                                       AND DATE(cm.date_range_to)
        )
      , cs.subscriber_id
      )
 )

兩種情況：

a) cm.date_range_from IS NOT NULL

AND cs.subscriber_id IN (
      SELECT cs1.subscriber_id
      FROM txt_campaign_subscriber cs1
      WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) 
                                    AND DATE(cm.date_range_to)
)


b) cm.date_range_from IS NULL

cs.subscriber_id = cs.subscriber_id

i.e. TRUE

這相當於：

(
AND cs.subscriber_id IN (
      SELECT cs1.subscriber_id
      FROM txt_campaign_subscriber cs1
      WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) 
                                    AND DATE(cm.date_range_to)
   )
AND cm.date_range_from IS NOT NULL
)
OR cm.date_range_from IS NULL

我們最終得到的所有和所有：

SELECT
   *
FROM
   txt_campaign_message cm
LEFT JOIN txt_campaign c ON
   c.id = cm.campaign_id
LEFT JOIN txt_message m ON
   m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs ON
   cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2 
 AND m.message_type = blast 
 AND c.active = 1
 AND (
     cs.subscriber_id IN (
         SELECT cs1.subscriber_id
         FROM txt_campaign_subscriber cs1
         WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) 
                                       AND DATE(cm.date_range_to)
       ) AND cm.date_range_from IS NOT NULL
     OR 
     cm.date_range_from IS NULL
 ) 
 AND cm.campaign_id = 1386;

完全未經測試，但它應該給你一個想法

您也可以考慮將 in-predicate 重寫為 JOIN

引用自：https://dba.stackexchange.com/questions/259953