查明最近 x 天是否没有创建任何记录

Question

假设我有一个数据库来跟踪浇水植物，其结构如下：

Table: "Plants"
ID     Plants
----------------------------------
1      Roses
2      Daisies
3      Sunflowers
4      Marigolds
5      Daffodils

Table: "Maintenance"
ID    Plant_ID    Activity    Date
-------------------------------------
1     1            Water      2011-09-09
2     1            Water      2011-08-02
3     2            Water      2011-08-15
4     3            Water      2010-07-01
5     4            Weed       2010-07-01

我正在尝试编写一个查询来告诉我们植物是否需要浇水，如果需要，浇水的程度如何。换句话说：如果植物在过去 30 天内没有浇水，则返回警告级别“黄色”；如果过去 60 天内没有浇水，则警告级别为“红色”。

到目前为止，这就是我所处的位置：

SELECT Plants.Plants, 
IF ( DATEDIFF(CURRENT_DATE, MAX(Maintenance.Date)) < 30 AND maintenance.type = "Water", '',
  IF ((DATEDIFF(CURRENT_DATE, MAX(Maintenance.Date)) >= 60 AND maintenance.type = "Water") or maintenance.date IS NULL, 'Red', 'Yellow')
) AS `Water Warning`,
IF ( DATEDIFF(CURRENT_DATE, MAX(Maintenance.Date)) < 30 and maintenance.type = "Weed" , '',
  IF ((DATEDIFF(CURRENT_DATE, MAX(Maintenance.Date)) >= 60 and maintenance.type = "Weed") or maintenance.date IS NULL, 'Red', 'Yellow')
) AS `Weeding Warning`
FROM `plants`
LEFT JOIN `maintenance` ON `maintenance`.`plant_id` = `plants`.`id`
GROUP BY `plants`.`id`;

但这会返回“Roses”的“红色”警告，而不是预期的空字符串，并且没有 Maintenance.Activity 的条件。请注意，同一查询还需要对除草和其他活动执行类似的功能，因此在 WHERE 子句中进行过滤可能不是答案。

这就是我所追求的：

Results
ID     Plants        "Water Warning"  "Weed Warning"
----------------------------------------------------
1      Roses               ""             "Red"
2      Daisies           "Yellow"         "Red"
3      Sunflowers         "Red"           "Red"
4      Marigolds          "Red"            ""
5      Daffodils          "Red"           "Red"

Answer 1

使用下面的语句，只需将表格与植物名称连接起来即可。

SELECT *, 

CASE
    WHEN DATEDIFF(CURRENT_DATE, DATE) >= 60 THEN 'RED'
    WHEN DATEDIFF(CURRENT_DATE, DATE) >= 30 THEN 'YELLOW'
END AS FLAG
FROM test_table

Answer 2

我真的希望有一个更优雅的解决方案，但这可行：

SELECT 
`plants`.`plants`,
IF((MAX(IF(`maintenance`.`type` = 'Water',
        `maintenance`.`date`,
        NULL)) IS NULL) OR ((DATEDIFF(CURRENT_DATE,
            MAX(IF(`maintenance`.`type` = 'Water',
                `maintenance`.`date`,
                NULL)))) > 30),
    (IF((MAX(IF(`maintenance`.`type` = 'Water',
            `maintenance`.`date`,
            NULL)) IS NULL) OR ((DATEDIFF(CURRENT_DATE,
                MAX(IF(`maintenance`.`type` = 'Water',
                    `maintenance`.`date`,
                    NULL)))) > 90),
        'red',
        'yellow')),
    '') AS `Water Warning`,
IF((MAX(IF(`maintenance`.`type` = 'Weed',
        `maintenance`.`date`,
        NULL)) IS NULL) OR ((DATEDIFF(CURRENT_DATE,
            MAX(IF(`maintenance`.`type` = 'Weed',
                `maintenance`.`date`,
                NULL)))) > 30),
    (IF((MAX(IF(`maintenance`.`type` = 'Weed',
            `maintenance`.`date`,
            NULL)) IS NULL) OR ((DATEDIFF(CURRENT_DATE,
                MAX(IF(`maintenance`.`type` = 'Weed',
                    `maintenance`.`date`,
                    NULL)))) > 90),
        'red',
        'yellow')),
    '') AS `Weed Warning`
FROM
`plants`
    LEFT JOIN
`maintenance` ON `maintenance`.`plant_id` = `plants`.`id`
GROUP BY `plants`.`id`;

Answer 3

实际数据

mysql> select * from Plants;
+------+------------+
| ID   | Plants     |
+------+------------+
|    1 | Roses      |
|    2 | Daisies    |
|    3 | Sunflowers |
|    4 | Marigolds  |
|    5 | Daffodils  |
+------+------------+

mysql> select * from Maintenance;
+----+----------+----------+------------+
| ID | Plant_ID | Activity | Date       |
+----+----------+----------+------------+
|  1 |        1 | Water    | 2011-09-09 |
|  2 |        1 | Water    | 2011-08-02 |
|  3 |        2 | Water    | 2011-08-15 |
|  4 |        3 | Water    | 2010-07-01 |
|  5 |        4 | Weed     | 2010-07-01 |
+----+----------+----------+------------+

SELECT
Plants.Plants,
COALESCE(
CASE mt.Activity='Weed'
  WHEN 1 THEN
    CASE DATEDIFF(CURRENT_DATE, mt.last_date) BETWEEN 0 AND 30
      WHEN 1 THEN ''
      ELSE
        CASE DATEDIFF(CURRENT_DATE, mt.last_date) BETWEEN 30 AND 60
        WHEN 1 THEN 'Yellow'
        ELSE NULL
        END
    END
END, 'Red') AS "Weed Warning",
COALESCE(
CASE mt.Activity='Water'
  WHEN 1 THEN
    CASE DATEDIFF(CURRENT_DATE, mt.last_date) BETWEEN 0 AND 30
      WHEN 1 THEN ''
      ELSE
        CASE DATEDIFF(CURRENT_DATE, mt.last_date) BETWEEN 30 AND 60
        WHEN 1 THEN 'Yellow'
        ELSE NULL
        END
    END
END, 'Red') AS "Water Warning"
FROM Plants
LEFT JOIN
( SELECT Plant_ID, Activity, MAX(Date) AS last_date
  FROM Maintenance
  GROUP BY Plant_ID, Activity) AS mt ON Plants.ID=mt.Plant_ID
GROUP BY Plants.ID;

结果

+------------+--------------+---------------+
| Plants     | Weed Warning | Water Warning |
+------------+--------------+---------------+
| Roses      | Red          |               |
| Daisies    | Red          |               |
| Sunflowers | Red          | Red           |
| Marigolds  | Red          | Red           |
| Daffodils  | Red          | Red           |
+------------+--------------+---------------+

OP注释

1. 您发布了错误的结果
2. 您引用了不存在的列 type
3. 您没有采取注意表名大小写敏感