MySQL - 如果日期已过期(即小于今天的日期)，如何将 "flag"列添加到选择查询？

Question

我有两张 table 。一份带有人名(司机)，另一份带有驾驶执照(驾照)的详细信息和过期时间。我需要合并这两个表并列出司机的姓名、身份证号码以及驾照是否过期。

我有以下返回所有司机及其驾照和到期时间的信息，但我想添加最后一列，根据返回的到期日期(时间戳)声明(过期/有效)。

SELECT d.name AS 'Driver Name', d.nric AS 'NRIC', l.expiry AS 'Expiry' FROM drivers d, licenses l WHERE d.driverID=licenses.driverID ORDER BY name ASC

我需要添加第四列来评估 l.expiry 是否“小于”今天，即过期。

输出应该是这样的:

Driver Name NRIC Expiry Validity
Joe Blooggs 1234 1/1/14 Valid
Joe Blooggy 1235 1/3/14 Invalid

有办法吗？

Answer 1

SQL Fiddle

MySQL 5.5.32 架构设置:

CREATE TABLE drivers
    (`driverID` int, `name` varchar(11), `nric` int)
;

INSERT INTO drivers
    (`driverID`, `name`, `nric`)
VALUES
    (1, 'Joe Blooggs', 1234),
    (2, 'Joe Blooggy', 1235)
;

CREATE TABLE licenses
    (`licenseID` int, `driverID` int, `expiry` int)
;

INSERT INTO licenses
    (`licenseID`,`driverID`, `expiry`)
VALUES
    (1,1, 1406217600),
    (2,1, 1409217600),
    (3,1, 1432656000),
    (4,2, 1406217600)
;

查询 1:

SELECT d.name AS 'Driver Name', d.nric AS 'NRIC', l.expiry AS 'Expiry',
CASE
    WHEN l.expiry < UNIX_TIMESTAMP(NOW()) THEN "Invalid"
    ELSE "Valid"
END AS Validity
FROM drivers d 
INNER JOIN licenses l ON d.driverID=l.driverID and l.licenseID = 
                                       (select licenseID 
                                          from licenses l2 
                                          where l.driverID = l2.driverID 
                                          order by expiry desc limit 1)

Results :

| DRIVER NAME | NRIC |     EXPIRY | VALIDITY |
|-------------|------|------------|----------|
| Joe Blooggs | 1234 | 1432656000 |    Valid |
| Joe Blooggy | 1235 | 1406217600 |  Invalid |