postgresql - 如何显示 SQL 从中找到 MIN 值的列的名称？

Question

我有一张产品和价格表。它包含 10 个不同的价格列。我能够成功找到所有 10 个价格列中的最低值。

因此，根据这些数据:

Prod. Name | Week1  | Week2  | Week N | Week 10
Pizza      | $1.29  | $1.29  | $1.42  | $1.01

我可以显示:

Prod. Name | Lowest Price
Pizza      | $1.01

但是我怎样才能添加另一列来显示最低值来自的列？

理想的输出应该是这样的:

Prod. Name | Lowest Price | From Week
Pizza      | $1.01        | 10

我用来显示输出的搜索查询是:

SELECT ProdName, LEAST(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) FROM 结果；

编辑:我忘了说我总共处理了大约 1,600 行数据。这肯定会使编码变得更加复杂!

Answer 1

不仅可以比较简单的值，还可以比较记录:

with t(x,y,z) as (values(1,3,2),(5,2,3))
select *, least((x,'x'::text),(y,'y'::text),(z,'z'::text)) from t;

╔═══╤═══╤═══╤═══════╗║ x │ y │ z │ least ║╠═══╪═══╪═══╪═══════╣║ 1 │ 3 │ 2 │ (1,x) ║║ 5 │ 2 │ 3 │ (2,y) ║╚═══╧═══╧═══╧═══════╝

If it is not enough then you could to convert it to jsonb for example and retrive the values in the separate rows:

with t(x,y,z) as (values(1,3,2),(5,2,3))
select *, j->>'f1' as val, j->>'f2' as fld
from t, to_jsonb(least((x,'x'::text),(y,'y'::text),(z,'z'::text))) as j;

╔═══╤═══╤═══╤══════════════════════╤═════╤═════╗║ x │ y │ z │          j           │ val │ fld ║╠═══╪═══╪═══╪══════════════════════╪═════╪═════╣║ 1 │ 3 │ 2 │ {"f1": 1, "f2": "x"} │ 1   │ x   ║║ 5 │ 2 │ 3 │ {"f1": 2, "f2": "y"} │ 2   │ y   ║╚═══╧═══╧═══╧══════════════════════╧═════╧═════╝

And for your data it could be:

select ProdName, j->>'f1' as val, j->>'f2' as fld
from results, to_jsonb(least((d1,'d1'),(d2,'d2'),(d3,'d3'),...,(d10,'d10')));