mysql - 显示占用床和空床 mySQL

Question

我的这个查询遇到了危机:

SELECT
    `tbl_beds`.`Bed_No`
    , `tbl_bedrest`.`Time_In`
    , `tbl_bedrest`.`Time_Out`
FROM
    `csbdb2`.`tbl_bedrest`
    INNER JOIN `csbdb2`.`tbl_beds` 
        ON (`tbl_bedrest`.`Bed_ID` = `tbl_beds`.`Bed_ID`)
WHERE 
    CURRENT_TIMESTAMP BETWEEN `tbl_bedrest`.time_in AND `tbl_bedrest`.time_out
    GROUP BY `tbl_beds`.`Bed_No`

我正在寻找的是获取所有床位并显示它们，即使它们在 CURRENT_TIMESTAMP 内是否被占用:

+-----------+---------------------+---------------------+
| bed_id    | time_in             | time_out            |
+-----------+---------------------+---------------------+
| BED000001 |                null |                null |
| BED000002 | 2016-01-14 09:00:00 | 2016-01-14 15:00:00 |
| BED000003 | 2016-01-14 06:00:00 | 2016-01-14 09:00:00 |
| BED000004 | 2016-01-14 09:55:07 | 2016-01-14 09:51:45 |
| BED000005 | 2016-01-14 10:00:18 | 2016-01-14 09:57:54 |
+-----------+---------------------+---------------------+

这是我的表格:

tbl_床

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Bed_ID   | varchar(10) | NO   | PRI | NULL    |       |
| Bed_No   | varchar(10) | YES  |     | NULL    |       |
| Building | varchar(10) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+

tbl_bedrest

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| BedRest_ID | varchar(10) | NO   | PRI | NULL    |       |
| Time_In    | datetime    | YES  |     | NULL    |       |
| Time_Out   | datetime    | YES  |     | NULL    |       |
| Case_ID    | varchar(10) | NO   | PRI | NULL    |       |
| Bed_ID     | varchar(10) | NO   | PRI | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Answer 1

您需要使用 LEFT JOIN，更改引用表的顺序并将 BETWEEN ... 条件移动到 ON 子句中。这应该有效。

SELECT
    `tbl_beds`.`Bed_No`
    , `tbl_bedrest`.`Time_In`
    , `tbl_bedrest`.`Time_Out`
FROM
    `csbdb2`.`tbl_beds` 
    LEFT JOIN `csbdb2`.`tbl_bedrest`
        ON (`tbl_bedrest`.`Bed_ID` = `tbl_beds`.`Bed_ID` AND CURRENT_TIMESTAMP BETWEEN `tbl_bedrest`.time_in AND `tbl_bedrest`.time_out)
    GROUP BY `tbl_beds`.`Bed_No`

即使在右表中找不到匹配的行，LEFT JOIN 也会为左表生成结果。