我有一個包含以下資料的 Microsoft SQL 表：
CustomerID Location1 Location2
788 A NULL
788 A B
788 B NULL
649 A NULL
649 NULL B
936 B NULL
我希望能夠查詢此表并僅回傳 CustomerID 和組合的唯一位置。
CustomerID Location1 Location2
788 A B
649 A B
936 B
Select Distinct CustomerID, MAX(Location1) L1, MAX(Location2) L2
FROM table
GROUP BY CustomerID
回報
CustomerID L1 L2
788 B B
649 B
936 B
很難解決這個問題。任何幫助將不勝感激。
uj5u.com熱心網友回復：
這應該可以解決 OP 的最后評論：
with u as
(select CustomerID, Location1 as L
from Table1
where Location1 is not null
union select CustomerID, Location2 as L
from Table1
where Location2 is not null)
(select customerID, min(L) as Location1, case when max(L) <> min(L) then max(L) end as Location2
from u
group by CustomerID)
小提琴
或者使用另一個答案中的相同技巧：
Select CustomerID
, MIN(coalesce(Location1,Location2)) L1
, case when MAX(coalesce(Location2,Location1)) <> MIN(coalesce(Location1,Location2)) then MAX(coalesce(Location1,Location2)) end L2
FROM table
GROUP BY CustomerID
uj5u.com熱心網友回復：
使用最小值和最大值：
Select CustomerID
, MIN(coalesce(Location1,Location2)) L1
, MAX(coalesce(Location2,Location1)) L2
FROM table
GROUP BY CustomerID
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399469.html
標籤：sql sql-server 查询语句
上一篇：Oracle中的資料型別