我有一個兩串列，如下所示
| 顧客姓名 | 客戶型別 |
|---|---|
| 吉姆·史密斯 | 零售 |
| 南希·瓊斯 | 批發的 |
| 邁克·威廉姆斯 | 零售 |
| ... | ... |
我希望能夠遍歷表以動態生成如下的 case 陳述句
CASE WHEN CUSTOMER_NAME = 'Jim Smith' THEN 'Retail'
WHEN CUSTOMER_NAME = 'Nancy Jones' THEN 'Wholesale'
WHEN CUSTOMER_NAME = 'Mike Williams' THEN 'Retail'
...
ELSE NULL END AS CUSTOMER_TYPE
做這個的最好方式是什么？
uj5u.com熱心網友回復：
這有點奇怪。您撰寫的 SQL 類似于說-
select case when CUSTOMER_NAME = CUSTOMER_NAME then CUSTOMER_TYPE end as CUSTOMER_TYPE
customer_name 始終等于 customer_name。您不妨將其重新撰寫為
select CUSTOMER_TYPE
查看您的完整查詢以查看您是否有這樣做的原因可能會有所幫助。但是這里的 case 陳述句大多是不必要的。
uj5u.com熱心網友回復：
這是一個解決方案。它也處理名稱和型別包含單引號的情況：
select 'case '
|| string_agg(replace(replace(replace('when customer_name = `@NM@` then `@TYP@`'
,'@NM@' ,replace(customer_name,'''',''''''))
,'@TYP@',replace(customer_type,'''',''''''))
,'`' ,'''')
,chr(10) || ' ')
|| chr(10) || 'end as customer_type'
from (values
('Jim Smith' , 'Retail')
,('Nancy Jones' , 'Wholesale')
,('Mike Williams' , 'Retail')
,('Radar O''Reilly', 'It''s a Military Contract')
)src(customer_name,customer_type)
結果：
case when customer_name = 'Jim Smith' then 'Retail'
when customer_name = 'Nancy Jones' then 'Wholesale'
when customer_name = 'Mike Williams' then 'Retail'
when customer_name = 'Radar O''Reilly' then 'It''s a Military Contract'
end as customer_type
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/334444.html
標籤：PostgreSQL