Блог

Главная - Вопросы по программированию - подзапрос в предложении where (mysql)

подзапрос в предложении where (mysql)

#mysql #subquery #where

#mysql #подзапрос #where-предложение

Вопрос:

 SELECT d.userID, (d.amountSuccessfulDeposits), (d.numberSuccessfulDeposits), cl.notes, d.asofdate, cl.send_date
FROM campaign_list cl
INNER JOIN deposit d
    on d.userid = cl.userid
WHERE cl.send_date > date(CURDATE()) - INTERVAL 7 DAY
    AND cl.send_date < date(CURDATE()) - INTERVAL 1 DAY
    AND d.asofdate > date(CURDATE()) - INTERVAL 7 DAY
    AND d.asofdate < date(CURDATE()) - INTERVAL 1 DAY
    and cl.notes in ('report11', 'report12', 'report13', 'report14', 'report15') 
    and d.asofdate > cl.send_date

После ввода идентификатора пользователя в кампанию (отчеты 11-15) запрос вычисляет количество внесенных депозитов. «send_date» — это дата ввода идентификатора пользователя в кампанию. Однако, если идентификатор пользователя продвинулся дальше в другой набор кампаний (отчеты 16-20), депозиты, внесенные после ввода 2-го набора кампаний (16-20), не должны учитываться в общем количестве запросов 1-го набора кампаний (11-15).

Пример:

Боб вводит отчет 11 на 1/20

Боб вносит 100 долларов на 1/21

Боб вводит отчет 16 на 1/24

Боб вносит 500 долларов на 1/25

Следовательно, приведенный выше запрос должен возвращать только 100 долларов для Bob.

 userID;  amountSuccessfulDeposits; numberSuccessfulDeposits; notes;    asofdate;     send_date
2575192;   100.00;                       1;                 report11    ;2016-01-21    ; 2016-01-20

Следующий запрос не сработал

 SELECT d.userID, (d.amountSuccessfulDeposits), (d.numberSuccessfulDeposits), cl.notes, d.asofdate, cl.send_date
FROM campaign_list cl
INNER JOIN deposit d
    on d.userid = cl.userid
WHERE cl.send_date > date(CURDATE()) - INTERVAL 7 DAY
    AND cl.send_date < date(CURDATE()) - INTERVAL 1 DAY
    AND d.asofdate > date(CURDATE()) - INTERVAL 7 DAY
    AND d.asofdate < date(CURDATE()) - INTERVAL 1 DAY
    and cl.notes in ('report11', 'report12', 'report13', 'report14', 'report15') 
    and d.asofdate > cl.send_date 
    AND (   
                (SELECT (ocl.send_date) 
                    FROM campaign_list ocl
                    WHERE ocl.userID = cl.userID
                        and ocl.notes in ('report16', 'report17', 'report18', 'report19', 'report20')                   
                ) > d.asofdate
        )

Комментарии:

1. Можем ли мы использовать некоторые данные таблицы и желаемый результат? Самый быстрый способ получить качественный ответ здесь.

2. как вы вводите числа в таблицу? когда я копирую и вставляю из клиента mysql, это беспорядок

3. Добавьте четыре пробела перед каждой строкой, что приведет к принудительному моноширинному тексту. Итак, просто создайте красивую таблицу в Блокноте, добавьте четыре пробела в начало каждой строки и вставьте ее в свой вопрос.

4. ОК. Пожалуйста, посмотрите мою редакцию.

Ответ №1:

Я бы использовал встроенное представление, чтобы получить «самую раннюю» дату отправки для последующих кампаний. (Неясно, существует ли какая-либо связь между конкретными кампаниями. Если Боб вводит какую-либо кампанию 16-20, то send_date этой кампании является «отключенным» для всех предыдущих кампаний 11-15)

Без полного понимания спецификации я немного неохотно предлагаю запрос, который будет «работать» для вас.

Но на основе данного запроса я бы сделал что-то вроде этого:

 SELECT d.userid
     , d.amountSuccessfulDeposits
     , d.numberSuccessfulDeposits
     , cl.notes
     , d.asofdate
     , cl.send_date
  FROM campaign_list cl

/* Здесь я бы создал встроенное представление, которое получает «самое раннее» send_date из последующих кампаний 16-20 для каждого идентификатора пользователя. И я делаю внешнее соединение с этим набором, что-то вроде этого */

   LEFT
  JOIN ( -- inline view to get earliest send_date
         SELECT ocl.userid
              , MIN(ocl.send_date) AS min_send_date
           FROM campaign_list ocl
          WHERE ocl.notes IN ('report16'
                             ,'report17'
                             ,'report18'
                             ,'report19'
                             ,'report20'
                             )
          GROUP BY ocl.userid
       ) oc
    ON oc.userid = cl.userid

/* При этом у нас будет «самый ранний» sent_date из связанной кампании. И теперь это связано с строкой из cl кампании. Теперь мы можем выполнить соединение с deposit таблицей. Я предпочитаю помещать все условия для столбцов d в ON предложение объединения, а не в WHERE предложение */

   JOIN deposit d
    ON d.userid     = cl.userid
   AND d.asofdate   > cl.send_date
   AND d.asofdate   > DATE(NOW())   INTERVAL -7 DAY
   AND d.asofdate   < DATE(NOW())   INTERVAL -1 DAY

/* теперь вот в чем фокус… */

    AND d.asofdate  <= IFNULL(oc.min_send_date,d.asofdate   INTERVAL 1 DAY)

включая условие, которое ограничивает строки deposit d , начиная asofdate с или после самой ранней send_date из последующей кампании. Сложная часть заключается в том, что нам нужно условие OR, когда при вводе кампании нет продолжения, самое раннее send_date будет NULL . Если значение oc.min_send_date не равно NULL , то мы просто используем это. Если оно равно NULL, мы подставляем значение, которое не будет ограничивать возврат строки из d . Значение, большее, чем d.asofdate будет работать.

Я использую <= (меньший или равный) тест. Если мы просто используем < тест, похоже, что a deposit с a asofdate , который находится в ту же дату, send_date что и будет ничейной землей… он не будет возвращен для предыдущей кампании, потому что у нас уже есть > (большее, чем) ограничение d.asofdate > cl.send_date .

Затем WHERE предложение с ограничениями на строки из cl */

  WHERE cl.send_date > DATE(NOW())   INTERVAL -7 DAY
   AND cl.send_date < DATE(NOW())   INTERVAL -1 DAY
   AND cl.notes    IN ('report11'
                      ,'report12'
                      ,'report13'
                      ,'report14'
                      ,'report15'
                      )

Это подход, который я бы выбрал.

Или ЗАВИСИМЫЙ ПОДЗАПРОС В ПРЕДЛОЖЕНИИ WHERE

Если есть какая-то причина, вам нужно включить зависимый подзапрос в WHERE предложение… вы могли бы записать его как EXISTS предикат … проверьте, есть ли какие-либо последующие кампании с send_date раньше, чем asofdate, например 

   AND NOT EXISTS ( SELECT 1
                     FROM campaign_list ocl
                    WHERE ocl.userID    = cl.userid
                      AND ocl.send_date < d.asofdate
                      AND ocl.notes    IN ('report16'
                                          ,'report17'
                                          ,'report18'
                                          ,'report19'
                                          ,'report20'
                                          )
                 )

Или, если есть какая-то причина, по которой вам нужно выполнить скалярное сравнение результатов, полученных из запроса (для сравнения меньше / больше, чем), тогда возврат из подзапроса должен быть скалярным … запрос должен возвращать один столбец и возвращать (не более) одну строку.

Чтобы убедиться, что возвращается не более одной строки, вы можете использовать агрегат (например MIN() , или вы могли бы использовать LIMIT предложение. И вы должны быть готовы обработать нулевое значение, которое будет возвращено, если строка не найдена.

  AND ( d.asofdate <= IFNULL( ( SELECT ocl.send_date 
                                 FROM campaign_list ocl
                                WHERE ocl.userID     = cl.userid
                                  AND ocl.send_date >= d.asofdate
                                  AND ocl.notes     IN ('report16'
                                                       ,'report17'
                                                       ,'report18'
                                                       ,'report19'
                                                       ,'report20'
                                                       )
                                ORDER BY ocl.send_date ASC
                                LIMIT 1
                             )
                     , d.asofdate
                     )

В MySQL могут быть некоторые ограничения, этот зависимый подзапрос может быть запрещен в WHERE предложении. Вы могли бы получить это в HAVING предложении наверняка. Но предложение HAVING оценивается почти последним при выполнении инструкции, после операции GROUP BY .