时间戳

首页 > 解决方案 > 从多个表中获取记录的问题

sql - 从多个表中获取记录的问题

问题描述

我有下表，我正在尝试获取异常结果中显示的输出

床

+----+------+--------------+
| id | name | bedfrid      |
+----+------+--------------+
| 1  | bed1 | 111111111111 |
+----+------+--------------+
| 2  | bed2 | 222222222222 |
+----+------+--------------+
| 3  | bed9 | 777777777777 |
+----+------+--------------+

部门病床

+----+------+-------+
| deptid | bedfrid  |
+----+------+-------+
| 1A  | 111111111111|
+----+------+-------+
| 1A  | 222222222222|
+----+------+-------+
| 5B  | 333333333333|
+----+------+-------+

地点

+----+------------+----------------------------+
| deptid | start time |  end time |    bedfrid |
+----+------------+----------------------------+
| 1A  | 0950       |   NULL    | 222222222222  |
+----+------------+----------------------------+
| 5B  | NULL       |   NULL    | 333333333333  |
+----+------+----------------------------------+
| 1A  | NULL      |   NULL    | 111111111111   |
+----+------------+----------------------------+

预期结果

+----+------------+----------------------------+
| 1A  | bed2       |  222222222222 |
+----+------------+----------------------------+

查询是：

select bed.name, bed.id 
from BED bed inner join
     DepartmentBeds dptBEDS
     on dptBEDS.bedfrid = bed.id inner join
     Location loc
     ON loc.bedfrid = dptBEDS.bedfrid
where dptBEDS.id = '1A' AND loc.starttime IS NOT NULL AND loc.endtime IS NULL

我是 SQL 新手。写了上面的查询，但我没有得到预期的结果。我哪里错了。

标签： sqlsql-server

解决方案

我希望这会有所帮助：

select loc.deptid, bed.name, loc.bedfrid
from (Location as loc inner join DepartmentBeds as dp
      on loc.bedfrid = dp.bedfrid) inner join bed
      on bed.bedfrid = dp.bedfrid
where loc.deptid = dp.deptid AND loc.starttime IS NOT NULL AND loc.endtime IS NULL

推荐阅读