同一帐户不同的状态,然后其他相同的状态。如何在 T-SQl 中执行此操作
Account Status
1000001185 A
1000001185 E
1000001185 E
1000001185 D
1000001777 E
1000001777 E
1000001777 E
1000001185 E
你可以做:
select Account, (case when min(Status) = max(Status)
then min(Status)
else 'other'
end)
from table t
group by Account;
这是一个SQL小提琴,表明这个解决方案是正确的。
试试这个我用了铅((函数
;WITH CTE(Account, [Status])
AS
(
SELECT '1000001185','A' UNION ALL
SELECT '1000001185','E' UNION ALL
SELECT '1000001185','E' UNION ALL
SELECT '1000001185','D' UNION ALL
SELECT '1000001777','E' UNION ALL
SELECT '1000001777','E' UNION ALL
SELECT '1000001777','E' UNION ALL
SELECT '1000001185','E'
)
SELECT Account,[Status],CASE WHEN [Status]<>LEAD([Status],1)OVER(PARTITION BY Account ORDER BY Account)
THEN 'Other' ELSE [Status] END [NewStatus]
FROM Cte
ORDER BY cte.[Status]
结果
Account Status NewStatus
-------------------------------------
1000001185 A A
1000001185 D Other
1000001185 E Other
1000001185 E E
1000001185 E Other
1000001777 E E
1000001777 E E
1000001777 E E