sql - 自连接与内部和外部连接查询

Question

我有一个表，该表的一列(属性)包含名字、姓氏、帐号以及与数据库中的事物相关的任何其他信息。另一列(attributeType)包含一个数字，指示属性是什么，例如1 可能是名字，2 可能是姓氏，3 可能是帐号等。还有另一列(结束日期)表明记录是否是最新的，那里有一个日期。通常它将当前设置为 9999 年，否则设置为过去的某个日期。描述同一事物的所有数据在另一列(实体)中也具有唯一值，因此实体列中具有相同编号的每个记录都将描述一个人。例如

entity  attribute  attributetype  enddate
------  ---------  -------------  --------
1       ben        1              9999-1-1
1       alt        2              9999-1-1
1       12345      3              9999-1-1
2       sam        1              9999-1-1
2       smith      2              9999-1-1
2       98765      3              1981-1-1

我想从上表中选择一个具有特定名字和姓氏的人，该姓名将是当前姓名，但如果不是则不输出帐号。假设该表名为 tblAccount，我对名称部分执行以下操作:

select ta1.attribute '1st Name', ta2.attribute 'last name'
from tblAccount ta1
inner join tblAccount ta2 on ta1.entity = ta2.entity
where ta1.attribute = 'sam' and ta2.attribute = 'smith'
      and ta1.attributetype = 1 and ta2. attributetype = 2
      and ta1.enddate > getdate() and ta2.enddate > getdate()

它按预期输出名字和姓氏，但是当我想包含帐号列时，我没有得到任何输出:

select ta1.attribute '1st Name', ta2.attribute 'last name', ta3.attribute 'account#'
from tblAccount ta1
inner join tblAccount ta2 on ta1.entity = ta2.entity
left join tblAccount ta3 on ta1.entity = ta3.entity
where ta1.attribute = 'sam' and ta2.attribute = 'smith'
      and ta1.attributetype = 1 and ta2. attributetype = 2
      and ta1.enddate > getdate() and ta2.enddate > getdate()
      and ta3.attributetype = 3 and ta3.enddate > getdate()

在上述情况下，我希望看到的是在 account# 列中没有任何内容的名字和姓氏输出，但它不是最新的。我做错了什么以及如何更正此查询？

Answer 1

您必须将日期比较移动到连接条件:

select ta1.attribute '1st Name'
    , ta2.attribute 'last name'
    , ta3.attribute 'account#'
from tblAccount ta1
inner join tblAccount ta2 
    on ta1.entity = ta2.entity
     and ta1.attributetype = 1 and ta2. attributetype = 2
     and ta1.enddate > getdate() and ta2.enddate > getdate()
left join tblAccount ta3 on ta1.entity = ta3.entity
      and ta3.attributetype = 3 and ta3.enddate > getdate()
where ta1.attribute = 'sam' and ta2.attribute = 'smith'

当它在 where 子句中时，如果没有帐户，它会将 getdate() 与 NULL 进行比较，这将返回 NULL。所以没有记录。

编辑:

针对valid对多条有效记录的关注，也为了方便代码维护:

DECLARE @FNAME VARCHAR(50) = 'sam'
    , @LNAME VARCHAR(50) = 'smith'
    , @now DATETIME2(7) = GETDATE();

SELECT 
    name.[1st Name]
    , name.[last name]
    , name.entity
    , 
        (
            select 
                top 1 
                ta3.attribute
            FROM tblAccount ta3 
            WHERE 
                ta3.entity = name.entity
                and 
                ta3.attributetype = 3 
                and 
                ta3.enddate > @now
            ORDER BY 
                ta3.enddate 
        )
FROM 
    (        
        select 
            ta1.attribute '1st Name'
            , ta2.attribute 'last name'
            , ta.entity
            , ROW_NUMBER()
                OVER(
                    PARTITION BY 
                        ta1.entity
                    ORDER BY 
                        ta1.enddate
                    ) r
        from 
            tblAccount ta1
        inner join tblAccount ta2 
            on 
            ta1.entity = ta2.entity
            and 
            ta2. attributetype = 2
            and 
            ta2.enddate > @now
            and 
            ta2.attribute = @LNAME
        where 
            ta1.attributetype = 1 
            and 
            ta1.attribute = @fname 
            and 
            ta1.enddate > @now
    ) name
WHERE    
    NAME.r = 1

;

此代码围绕每个名字/姓氏一个实体的隐含假设工作，并且在执行时间之后恰好有一个结束日期。这些变量对存储过程更友好一点，并允许您更改“截至”日期。如果您坚持使用 EAV，您可能需要存储过程。我正在考虑在相关日期之后结束的第一条记录，假设任何后来的记录都应该只在该记录到期后才有效。也许这是矫枉过正，因为它超出了 OP 问题的范围，但这是一个有效的观点。

我说“坚持使用 EAV”。虽然 EAV 并不总是坏的；也没有在背后开枪。在任何一种情况下，如果您希望通过陪审团，您最好有充分的理由。在 NoSQL 存储模式中它很好，但 EAV 通常是 RDBMS 范例的糟糕实现模式。

尽管从 OP 后来的评论来看，他似乎找到了一个更好的理由。