mysql LAST_DAY()只读取1个子查询结果，如何处理所有结果？使用连接？

Question

我有一个像这样的保险单表:

+-------------------------------------------------------------+
| id | cancellation_val | cancellation_interval | expire_date |
+-------------------------------------------------------------+
| 1  | 30               | day                   | 2019-06-09  |
| 2  | 2                | month                 | 2019-12-01  |
+-------------------------------------------------------------+

我需要获取从今天开始以及 4 个月内即将到期的保单 ID，计算该月的最后一天，如下伪代码:

'今天' <= LAST_DAY(expire_date - Cancel_val/interval ) < '今天 + 4 个月'

作为一个非专业人士，我认为我应该使用 JOIN，但我不知道如何使用，经过几天的尝试，我取得的唯一成果是:

SELECT LAST_DAY(
    DATE_FORMAT(
        STR_TO_DATE(
            (SELECT CASE cancellation_interval 
            WHEN "day" THEN date_sub(expire_date, INTERVAL cancellation_val DAY)
            WHEN "month" THEN date_sub(data_scadenzaexpire_date, INTERVAL cancellation_val MONTH)       
            END
            AS newDate
            FROM insurance WHERE id=2
            ), '%Y-%m-%d'
        ), '%Y-%m-%d'
    )
)

这是有效的，但我不需要“WHERE id=2”子句(因为我需要处理表的所有行)，如果删除它，我会收到错误“子查询返回超过 1 行”。
那么我该如何继续呢？并使用结果保持在“今天”和“今天 + 4 个月”之间？

我认为通过某种 JOIN 我可以以更简单的方式做到这一点，但我不知道如何做。

谢谢大家

Answer 1

问题在于查询的结构，而不是 LAST_DAY 函数。

我们想要返回满足某些条件的行的id值。因此查询的形式如下:

SELECT t.id 
     , ... 
  FROM insurance t 
 WHERE ... 
HAVING ...

引入另一个SELECT关键字基本上引入了一个子查询。子查询有限制...在 SELECT 列表中，子查询只能返回单列和(最多)单行。

所以让我们放弃那个额外的 SELECT 关键字。

我们可以将 newdate 派生为 SELECT 列表的表达式，然后我们可以在 HAVING 子句中引用该派生列。规范说我们想要返回 id 值，因此我们将其包含在 SELECT 列表中。我们不必返回任何其他列，但为了测试/调试，返回用于派生 newdate 列的值可能会很有用。

类似这样的事情:

SELECT t.id
     , LAST_DAY(
         CASE t.cancellation_interval
         WHEN 'day'    THEN  t.expire_date - INTERVAL t.cancellation_val DAY
         WHEN 'month'  THEN  t.expire_date - INTERVAL t.cancellation_val MONTH
         ELSE t.expire_date
         END
       ) AS newdate
     , t.expire_date
     , t.cancellation_interval
     , t.cancellation_val
  FROM insurance t
HAVING newdate >= DATE(NOW()) 
   AND newdate <= DATE(NOW()) + INTERVAL 4 MONTH
 ORDER 
    BY newdate ASC 

<小时/>

我们不必在 SELECT 列表中包含 newdate；我们可以将 HAVING 子句中出现的 newdate 替换为表达式。

我们还可以使用内联 View 来“隐藏”newdate 列的派生

SELECT v.id
     , v.newdate
  FROM ( SELECT t.id
              , LAST_DAY(
                  CASE t.cancellation_interval
                  WHEN 'day'    THEN  t.expire_date - INTERVAL t.cancellation_val DAY
                  WHEN 'month'  THEN  t.expire_date - INTERVAL t.cancellation_val MONTH
                  ELSE t.expire_date
                  END
                ) AS newdate
           FROM insurance t
       ) v
 WHERE v.newdate >= DATE(NOW()) 
   AND v.newdate <= DATE(NOW()) + INTERVAL 4 MONTH
 ORDER 
    BY v.newdate ASC