代码之家 › 专栏 › 技术社区 › Daniel McWilliams

找不到记录的SQL联接

self-join sql-server sql

Daniel McWilliams · 技术社区 · 10 年前

所以我尝试使用一个表和多个选择进行连接。

我用的桌子看起来像这样。。。

    Floor No. | Apartment No. | Bathroom No. | Size
        1     |       1       |       1      |  30
        1     |       1       |       2      |  20
        1     |       2       |       1      |  30
        1     |       2       |       2      |  40
        1     |       2       |       3      |  60
        2     |       1       |       1      |  30
        2     |       1       |       2      |  20
        2     |       2       |       1      |  30
        2     |       2       |       2      |  40
        2     |       2       |       3      |  60

所以基本上,每一层都有很多公寓,每一间公寓都有很多不同大小的浴室。我正在尝试返回类似于这样的结果。。

    Floor No. | APT1A | APT1B | APT2A | APT2B | APT2C
       1      |   30  |   20  |   30  |   40  |   60
       2      |   30  |   20  |   30  |   40  |   60

到目前为止,我已经得到了一条有效的SQL语句。。

    SELECT DISTINCT A.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
    FROM BathroomTable A
    inner join BathroomTable B on a.FloorNumber  = B.FloorNumber
    inner outer join BathroomTable C on a.FloorNumber = C.FloorNumber
    WHERE b.ApartmentNumber = 'APT1' AND b.BathroomNumber = 1
    AND (C.ApartmentNumber = 'APT1' AND C.BathroomNumber = 2)

连接也是如此,只要我连接数据库中存在的记录,连接就可以工作。。然而,有时公寓1中只有一个卫生间,sql查询将尝试查找第二个卫生间的数据,然后不会返回任何结果。如果找不到空值,是否有方法插入空值?

谢谢,我希望你能理解我想做什么。。

2 回复 | 直到 10 年前

Jens 10 年前

试试看:

SELECT DISTINCT B.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
FROM ApartementTable A
inner join BathroomTable B on a.ApartmentNumber  = B.ApartmentNumber AND b.BathroomNumber = 1
LEFT join BathroomTable C on a.ApartmentNumber = C.ApartmentNumber AND C.BathroomNumber = 2
WHERE A.ApartmentNumber = 'APT1'

mehdi lotfi 10 年前

SELECT DISTINCT A.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
FROM BathroomTable A
OUTER join BathroomTable B on a.FloorNumber  = B.FloorNumber
OUTER join BathroomTable C on a.FloorNumber = C.FloorNumber
WHERE b.ApartmentNumber = 'APT1' AND b.BathroomNumber = 1 AND (C.ApartmentNumber = 'APT1' AND C.BathroomNumber = 2)