MySQL:在 SELECT 语句中使用 IF 条件比较日期

Question

示例数据:

db1.location详细信息表

| id | locationUID | locationName |
|----|-------------|--------------|
| 1  | L0001       | Site A       |
| 2  | L0002       | Site B       |
| 3  | L0003       | Site C       |
| 3  | L0004       | Site D       |

db2.HealthData 表

| id | locationID  | Date_Time        | memUsage |
|----|-------------|------------------|----------|
| 1  | L0001       | 2018-09-10 11:43 |   35     |
| 2  | L0002       | 2018-09-10 08:22 |   39     |
| 3  | L0003       | 2018-09-10 14:44 |   43     |
| 4  | L0004       | 2018-09-10 16:01 |   72     |
| 5  | L0001       | 2018-09-12 01:26 |   50     |
| 6  | L0002       | 2018-09-12 03:15 |   32     |

我有一个疑问:

SELECT DISTINCT db1.locationDetails.locationUID,
    db1.locationDetails.locationName,
    MAX(db2.HealthData.Date_Time),
    db2.HealthData.memUsage,
    IF(DATE(db2.HealthData.Date_Time) = '2018-09-12', "ON", "OFF") AS Status
FROM db1.locationDetails
LEFT JOIN db2.HealthData
ON db1.locationDetails.locationUID = db2.HealthData.locationID
GROUP BY db1.locationDetails.locationUID

根据我的理解，如果日期等于 2018-09-12，“状态”列将显示“ON”，但不知何故，无论 Date_Time 列中的值是否等于查询中指定的日期值。

谁能告诉我这里出了什么问题吗？提前致谢。

预期输出:

| locationUID | locationName | Date_Time       | memUsage | Status |
|-------------|--------------|-----------------|----------|--------|
| L0001       | Site A       |2018-09-12 01:26 |   50     | ON     |
| L0002       | Site B       |2018-09-12 03:15 |   32     | ON     |
| L0003       | Site C       |2018-09-10 14:44 |   43     | OFF    |
| L0004       | Site D       |2018-09-10 16:01 |   72     | OFF    |

Answer 1

使用子查询得到你想要的结果:

   select x.locationuid,x.locationname,maxitme, memusage, case when date(maxtime)='2018-09-12' then 'ON' else 'OFF' end as status
    from db1.locationDetails x
    inner join
    (select a.locationuid,maxtime,memusage
    from 
    (SELECT locationUID,MAX(Date_Time) as maxtime FROM db2.HealthData group by locationUID)a
    inner join db2.HealthData b on a.locationuid=b.locationuid)y
    on x.locationuid=y.locationuid