我有一個包含四個不同貨幣列的表的 SQL 資料庫，例如，我需要確定每條記錄在這四列中有多少種不同的貨幣。
| 記錄編號 | 電流1 | 電流2 | 電流3 | Curr4 | 貨幣數量 |
|---|---|---|---|---|---|
| 1 | 英鎊 | 美元 | 英鎊 | 英鎊 | 2 |
| 2 | 英鎊 | 歐元 | 英鎊 | 美元 | 3 |
| 3 | 英鎊 | 英鎊 | 英鎊 | 英鎊 | 1 |
| 4 | 英鎊 | 英鎊 | 英鎊 | 歐元 | 2 |
如何根據每條記錄確定“CurrencyCount”/貨幣數量？我想不出如何解決這個問題
感謝您的任何幫助
uj5u.com熱心網友回復：
您可以使用表值建構式從外行參考列，然后COUNT (DISTINCT- 不需要任何擴展和折疊GROUP BY
SELECT *,
CurrrencyCount = (SELECT COUNT (DISTINCT Currency) FROM (VALUES (CURR1), (CURR2), (CURR3), (CURR4)) AS x (Currency))
FROM YourTable
uj5u.com熱心網友回復：
這將完成作業。但不確定如果有大量資料，性能如何。
WITH CTE as (
SELECT '1' as RecID,'GBP' as CURR1,'USD' as CURR2,'GBP' as CURR3, 'GBP' as CURR4
UNION ALL
SELECT '2' ,'GBP' ,'EUR' ,'GBP','USD'
UNION ALL
SELECT '3' ,'GBP' ,'GBP' ,'GBP','GBP'
UNION ALL
SELECT '4' ,'GBP' ,'GBP' ,'GBP','EUR'
)
SELECT
RecID
,CURR1,CURR2,CURR3,CURR4
,COUNT(DISTINCT Currency) as CurrrencyCount
FROM CTE
CROSS APPLY (VALUES (CURR1), (CURR2), (CURR3), (CURR4)) as x (Currency)
GROUP BY RecID,CURR1,CURR2,CURR3,CURR4
uj5u.com熱心網友回復：
只運行這個它也可以作業
SELECT
RecordID
,Curr1,Curr2,Curr3,Curr4
,COUNT(DISTINCT Currency) as Currency
FROM [TABLE_NAME]
CROSS APPLY (VALUES (Curr1), (Curr2), (Curr3),(Curr4)) as x (Currency)
GROUP BY RecordID,Curr1,Curr2,Curr3,Curr4
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/377493.html