mysql - SQL - 对另一个查询的结果执行查询？

Question

我有一个包含日期值的列(varchar)，我需要找到 future 30 天内到期的日期。

    ExpiringDate===================20171208,20171215,samples20171130,testedN/ANo(empty row)

So, First I need to get values before comma. On the resultset, I need to filter out rows that has only numbers(no 'N/A' or 'No' or empty rows) & then I need to filter those dates which are expiring in next 30 days.

Edited

I have tried the following & resultset seems to be inappropriate

SELECT
  DocName,
  CategoryName,
  AttributeName,
  CAST(SUBSTRING_INDEX(AttributeValue, ',', 1) AS DATE) AS ExpiredDate
FROM myDB
WHERE (AttributeName = 'Date of last vessel OVID' OR AttributeName = 'Next Statutory docking' OR
       AttributeName = 'Last statutory docking') AND AttributeValue LIKE '%[^0-9]%' AND
      DATEDIFF(now(), AttributeValue) <= 30;

Answer 1

因为您不仅将日期存储为文本，而且将这些日期与完全非日期信息混合在一起，这使事情变得复杂。在这种情况下，我们可以进行两项检查，一是确保记录以实际预期日期开始，二是确保日期差异在从现在起的 30 天内。

SELECT ExpiringDate
FROM
(
    SELECT ExpiringDate
    FROM yourTable
    WHERE ExpiringDate REGEXP '^[0-9]{8}'
) t
WHERE
    DATEDIFF(LEFT(ExpiringDate, 8), NOW()) BETWEEN 0 AND 30;

请注意，我使用子查询首先删除甚至没有可解析日期的行。原因是如果没有为两个参数传递有效日期，DATEDIFF 将会出错。

Demo