如何创建有条件的列?

Question

假设我有一个包含3列的表- id，数量，价格。量是1 - 10000。我想分割/组5部分的2d列，并添加一个varchar列与这些间隔的解释:

1-200     --- critical
201-400   --- small
401-600   --- medium
601-800   --- enough
801-1000  --- ok

因此，输出包括4列- id，数量，价格和说明，按数量分组。如何用Postgres做到这一点?

Answer 1

您可以在视图中使用CASE或使用CASE添加虚拟列，或者在查询中简单地使用CASE。

Select 
id, 
quantity, 
price,
case quantity
When <= 200 then 'critical'
When <= 400 then 'small'
When <= 600 then 'medium'
When <= 800 then 'enough'
Else 'ok' as status
From myTable;

Answer 2

SELECT id, quantity, price,
CASE
WHEN (quantity <= 200) THEN 'critical' 
WHEN (quantity BETWEEN 201 AND 400) THEN 'small' 
WHEN (quantity BETWEEN 401 AND 600) THEN 'medium' 
WHEN (quantity BETWEEN 601 AND 800) THEN 'enough' 
ELSE 'ok' 
END AS explanation
FROM ...