代码之家 › 专栏 › 技术社区 › user1184100

从多个表中提取记录时出现问题

sql-server sql

user1184100 · 技术社区 · 6 年前

我有下面的表格,我正试图得到例外结果中显示的输出。

床

+----+------+--------------+
| 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。写了上面的查询,但我没有得到预期的结果。我哪里做错了。

3 回复 | 直到 6 年前

Larnu 6 年前

这是在假设操作的预期结果是 错误的 . 他们的预期结果是 [name] 作为 'bed1' 但是, [bedfrid] 值为 222222222222 . “床上用品” 值为 111111111111 因为它 [贝弗里德] ,所以我不知道这两个值是如何关联的。因此,这是一个猜测,OP意味着 'bed2' 不是 “床上用品” ( 这已经被纠正了 ):

USE Sandbox;
GO

CREATE TABLE dbo.Bed (id int,
                      [name] varchar(4),
                      bedfrid bigint);
CREATE TABLE dbo.DepartmentBed (deptid char(2),
                                bedfrid bigint);
CREATE TABLE dbo.[Location](deptid char(2),
                            starttime char(4),
                            endtime char(4),
                            bedfrid bigint);

INSERT INTO dbo.Bed (id,
                     [name],
                     bedfrid)
VALUES (1,'bed1',111111111111),
       (2,'bed2',222222222222),
       (3,'bed9',777777777777);

INSERT INTO dbo.DepartmentBed (deptid,
                               bedfrid)
VALUES ('1A',111111111111),
       ('1A',222222222222),
       ('5B',333333333333);

INSERT INTO dbo.[Location] (deptid,
                            starttime,
                            endtime,
                            bedfrid)
VALUES ('1A','0950',NULL,222222222222),
       ('5B',NULL,NULL,333333333333),
       ('1A',NULL,NULL,111111111111);
GO
SELECT DB.deptid,
       B.[name],
       B.bedfrid
FROM dbo.Bed B
     JOIN dbo.DepartmentBed DB ON B.bedfrid = DB.bedfrid
     JOIN dbo.[Location] L ON DB.bedfrid = L.bedfrid
                          AND DB.deptid = L.deptid
WHERE DB.deptid = '1A'
  AND L.starttime IS NOT NULL
  AND L.endtime IS NULL;

GO

DROP TABLE dbo.Bed;
DROP TABLE dbo.DepartmentBed;
DROP TABLE dbo.[Location];

Coral Kashri 6 年前

我希望这有助于:

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

Pawel Czapski 6 年前

我认为你的连接是错误的,下面应该是正确的(测试它):

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