合并SQL查詢結果，其中任何為空

我正在嘗試使用 SQL 查詢處理大部分資料清理作業。有時我們只需要一個人來撥打電話，但在資料中唯一的差異是資料丟失的情況下，我希望能夠只回傳一行。假設我們有以下資料：

| FirstName | LastName | Email              | Phone     |
|:--------- |:-------- |:------------------ |:--------- |
| Joe       | Bloggs   | joe.bl@domain.com  | 9412 3456 |
| Bob       | Jones    | bob.jones@abc.com  | 9498 7654 |
| Bob       | Jones    | bob.jones@abc.com  |           |
| Mary      | Flubbs   |                    |           |
| Mary      | Flubbs   |                    | 9411 1111 |
| Mary      | Flubbs   | marymary@corp.com  |           |
| Taylor    | Minns    | taylor@minnsco.com | 9499 8877 |
| Taylor    | Minns    |                    | 9477 8899 |
| Taylor    | Minns    |                    |           |

喬只有一個記錄。除了缺少電話號碼外，Bob 的記錄是相同的。Mary's 也沒有差異，只有空的。Taylor 有兩個不同的電話號碼，因此仍然需要兩個記錄，如下所示：

| FirstName | LastName | Email              | Phone     |
|:--------- |:-------- |:------------------ |:--------- |
| Joe       | Bloggs   | joe.bl@domain.com  | 9412 3456 |
| Bob       | Jones    | bob.jones@abc.com  | 9498 7654 |
| Mary      | Flubbs   | marymary@corp.com  | 9411 1111 |
| Taylor    | Minns    | taylor@minnsco.com | 9499 8877 |
| Taylor    | Minns    | taylor@minnsco.com | 9477 8899 |

如果我只是去SELECT DISTINCT...它會顯示空。如果我使用，MAX()否則MIN()我會丟失資料。有沒有辦法像這樣折疊記錄而不會丟失不同的資料？

uj5u.com熱心網友回復：

您可以使用Cross Apply或Inner Join。假設相同的名字是相同的人

交叉申請

declare @mytable table (Firstname varchar(50), LastName varchar(50), Email varchar(50), Phone varchar(50))

insert into @mytable
values 
('Joe'       , 'Bloggs'   , '[email protected]'  , '9412 3456') ,
('Bob'       , 'Jones'    , '[email protected]'  , '9498 7654') ,
('Bob'       , 'Jones'    , '[email protected]'  , '') ,
('Mary'      , 'Flubbs'   , ''                   , '') ,
('Mary'      , 'Flubbs'  ,  ''                  , '9411 1111') ,
('Mary'      , 'Flubbs'   , '[email protected]'  , '') ,
('Taylor'    , 'Minns'    , '[email protected]' , '9499 8877') ,
('Taylor'    , 'Minns'    ,  ''                  , '9477 8899') ,
('Taylor'    , 'Minns'    ,  ''                  ,  '')

select distinct firstname, lastname, x.Email, y.Phone
from @mytable t
cross apply (  -- this statement will only filter emails
    select email
    from @mytable  e
    where nullif(email,'') is not null
    and e.Firstname  = t.Firstname
    and e.LastName = t.LastName
) x
cross apply ( -- this statement will only filter phone
    select phone
    from @mytable p
    where nullif(phone,'') is not null
    and p.Firstname  = t.Firstname
    and p.LastName = t.LastName
) y

內部聯接

select distinct t.firstname, t.lastname, x.Email, y.Phone
from @mytable t
inner join (
    select Firstname, LastName, email
    from @mytable  e
    where nullif(email,'') is not null
) x on x.Firstname  = t.Firstname
    and x.LastName = t.LastName
inner join (
    select Firstname, LastName, phone
    from @mytable  p
    where nullif(phone,'') is not null
) y on y.Firstname  = t.Firstname
    and y.LastName = t.LastName

uj5u.com熱心網友回復：

您可以使用CASE運算式 onEmail和Phone在當前值（如果有）之間進行選擇，或者如果當前行為空或為空，則為當前行查找一個值。

SELECT *
FROM (
    SELECT 
        FirstName
    ,   LastName
    ,   CASE 
            WHEN ISNULL(p1.Email, '') = '' 
            THEN (
                    SELECT MAX(p2.Email) 
                    FROM Person p2
                    WHERE  
                        p1.FirstName = p2.FirstName 
                    AND p1.LastName  = p2.LastName 
                    AND ISNULL(p2.Email, '') <> ''
                )
            ELSE p1.Email 
        END Email 
    ,   CASE 
            WHEN ISNULL(p1.Phone, '') = '' 
            THEN (
                    SELECT MAX(p2.Phone) 
                    FROM Person p2
                    WHERE  
                        p1.FirstName = p2.FirstName 
                    AND p1.LastName  = p2.LastName 
                    AND ISNULL(p2.Phone, '') <> ''
                )
            ELSE p1.Phone 
        END Phone 
    FROM 
        Person p1
) e 
GROUP BY 
    FirstName
,   LastName
,   Email
,   Phone

SQL小提琴

標籤：sql sql-server 查询语句 总计的

上一篇：如何在分隔符和輸入新列之間多次提取字串的一部分T-SQL

下一篇：Django會話如何區分用戶？