mysql：在select语句中使用if条件比较日期

0h4hbjxa 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(495)

样本数据：
db1.locationdetails表

| 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，“状态”列将显示“开”，但不管日期\时间列中的值是否等于查询中指定的日期值，它总是返回“关”。
有人能告诉我这里怎么了吗？提前谢谢。
预期产量：

| 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    |

mysql mysql-5.6 select if-statement

来源：https://stackoverflow.com/questions/52288969/mysql-comparing-date-using-if-condition-in-select-statement

2条答案

按热度按时间

u2nhd7ah1#

使用子查询获得所需结果：

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

赞(0）回复(0）举报 2021-06-21

2ledvvac2#

add group by db1.locationdetails.locationuid、db2.healthdata.id

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,db2.HealthData.id

赞(0）回复(0）举报 2021-06-21

我来回答

mysql：在select语句中使用if条件比较日期

2条答案

相关问题

热门标签

最新问答