SQL 查询：连接两个单独行中的两个字段

Question

我有以下两个表：

USER
FID  UID     VALUE
4    3       John
3    3    Doe
4    4       Jack
3    4       Russel

应该相当清楚，FID 3 = 姓氏，FID 4 = 姓名。

DATEDATA
UID  DATE  
3    1234
4    4321

我想连接这两个表，这样我最终会得到这样的结果：

UID  DATE  NAME   SURNAME
3    1234  John   Doe
4    4321  Jack   Russel

或者... 或者...

UID  DATE  FULLNAME
3    1234  John Doe
4    4321  Jack Russel

有 SQL 专家吗？

这就是我到目前为止所拥有的：

SELECT UID, DATE, VALUE 
    from DATEDATA as D 
    left join USER as U 
    on U.uid = D.uid where fid = 3 OR fid = 4

但这给了我这个：

UID  DATE  VALUE
3    1234  Doe
3    1234  John
4    4321  Russel
4    4321  Jack

有人吗？

Answer 1

SELECT D.UID, DATE, U.VALUE + ' ' + U2.Value as fullName
from DATEDATA as D 
left join USER as U on U.uid = D.uid and U.fid = 3
left join USER as U2 on U2.uid = D.uid and U2.fid = 4

尽管只要第一个或最后一个为 NULL，这可能会给您一个 NULL 名称。如果您可以接受用户在系统中只有一个名称或另一个名称的情况，那么在这种情况下您可能需要使用 ISNULL 使任一名称成为空字符串。

Answer 2

SELECT A.UID, DATEDATA.DATE, A.VALUE, B.VALUE from DATEDATA, USER A, USER B 
WHERE A.UID = B.UID AND A.FID = 3 AND B.FID = 4 AND DATEDATA.UID = A.UID

Answer 3

select 
  D.UID
, D.DATE
, isnull(U.VALUE, '') 'firstName'
, isnull(U2.VALUE, '') 'surName'
from
  DateData D
left join User U on U.uid = D.uid and U.fid = 3
left join User U2 on U2.uid = D.uid and U2.fid = 4

Answer 4

您可以使用类似以下内容。它假设您始终有名字。如果您总是有一些字段并且它不是名字，请将其设为第一个字段并重新调整连接。如果您无法保证价值，请告诉我们，我们将提供更困难的解决方案。

select d.uid,COALESCE(d.date,'none entered'),COALESCE(frst.value,'') as NAME,COALESCE(lst.value,'') as SURNAME
from
user frst (NOLOCK)
left join user lst (NOLOCK) on lst.uid=frst.uid
left join datedata d (NOLOCK) on d.uid = frst.uid
where
frst.fid=4
AND lst.fid=3