sql - 查询语句怎么写？

Question

我有一个包含客户的表(该表的目标是能够在没有 DB-Update 的情况下添加字段)。该表如下所示:

CustId     Property     PropertyValue  

1          Name         Smith  
1          Email        smith@gmail.com  
2          Name         Donalds  
2          Email        donalds@gmail.com  
3          Name         john

(客户 3 在表中没有“电子邮件”条目)

预期结果:我想为每个客户(邮件)获取一行，如果客户没有电子邮件，则仍然显示一行 NULL。

CustId     Property     PropertyValue  

1          Email        smith@gmail.com  
2          Email        donalds@gmail.com  
3          Email        NULL

有人解决了吗？

Answer 1

查询 1

Select t1.CustId 
     , ISNULL(t2.Property ,'Email') AS Property 
     , t2.PropertyValue
FROM TableName t1 
LEFT JOIN TableName t2 ON t1.CustId = t2.CustId
                      AND t2.Property = 'Email'
WHERE t1.Property = 'Name'

结果集 1

╔════════╦══════════╦═══════════════════╗
║ CustId ║ Property ║   PropertyValue   ║
╠════════╬══════════╬═══════════════════╣
║      1 ║ Email    ║ smith@gmail.com   ║
║      2 ║ Email    ║ donalds@gmail.com ║
║      3 ║ Email    ║ NULL              ║
╚════════╩══════════╩═══════════════════╝

查询 2

另一个更易读的结果集的查询应该看起来像......

Select t1.CustId 
     , t1.PropertyValue [CustomerName]
     , t2.PropertyValue [CustomerEmail]
FROM TableName t1 
LEFT JOIN TableName t2 ON t1.CustId = t2.CustId
                      AND t2.Property = 'Email'
WHERE t1.Property = 'Name'

结果集 2

╔════════╦══════════════╦═══════════════════╗
║ CustId ║ CustomerName ║   CustomerEmail   ║
╠════════╬══════════════╬═══════════════════╣
║      1 ║ Smith        ║ smith@gmail.com   ║
║      2 ║ Donalds      ║ donalds@gmail.com ║
║      3 ║ john         ║ NULL              ║
╚════════╩══════════════╩═══════════════════╝