How to get column which has least values among them

CREATE TABLE exmp_test
(
id int, 
v1 int,
v2 int,
v3 int,
v4 int
)

SELECT * FROM exmp_test

id  v1  v2  v3  v4
1   2   4   6   7
1   4   77  3   8

I want to add the value of the [id] column to (whichever has least value for v1 ,v2 ,v3 ,v4) for each row.

我想为每一行添加[id]列的值(以v1,v2,v3,v4的最小值为准)。

As an example, for the first row, the [id] value should be add to v1 (because it has the lowest value). For the second row, the [id] value should be add to v3 (because it has the lowest value).

例如,对于第一行,[id]值应该添加到v1(因为它具有最低值)。对于第二行,[id]值应添加到v3(因为它具有最低值)。

How would I write the SQL to do this?

如何编写SQL来执行此操作?

2 个解决方案

#1

You could normalize the table in a CTE (common table expression), and then select the row with the minimum value. Based on the comment below your question, I've added priority for v1.

您可以在CTE(公用表表达式)中规范化表,然后选择具有最小值的行。根据您的问题下面的评论,我已经为v1添加了优先级。

;with Normal as (
    select id, v1 as value, 1 as prio from YourTable
    union all select id, v2, 2 as prio from YourTable
    union all select id, v3, 3 as prio from YourTable
    union all select id, v4, 4 as prio from YourTable
)
select top 1 id, value
from Normal
where value = (
    select min(value) from Normal
)
order by prio

After re-reading your question, here's a way to look at the lowest value per row and add the id field to that:

在重新阅读您的问题之后,这里有一种方法可以查看每行的最低值并将id字段添加到:

update t1
set v1 = v1 + case when mincol = 1 then id else 0 end
,   v2 = v2 + case when mincol = 2 then id else 0 end
,   v3 = v3 + case when mincol = 3 then id else 0 end
,   v4 = v4 + case when mincol = 4 then id else 0 end
from (
    select id, v1, v2, v3, v4,
        case 
          when v1 <= v2 and v1 <= v3 and v1 <= v4 then 1
          when v2 <= v3 and v2 <= v4 then 2
          when v3 <= v4 then 3
          else 4
         end as mincol
    from YourTable
) t1

#2

You can do the same using UVPIVOT

你可以使用UVPIVOT做同样的事情

Test data

declare @exmp_test table(id int, v1 int,v2 int,v3 int,v4 int)
insert into @exmp_test 
 select 1,   2 ,  4 ,  6,   7 union all
 select 1 ,  4 ,  77 , 3  , 8 union all
 select 2 ,  4 ,  16 , 1  , 8

Query

;with cte as(select row_number() over(order by id) as rn,t.v1 + t.v2 + t.v3 + t.v4 as sumallversions,t.* from @exmp_test t)
    ,unpvtcte as(select rn,id as versions,vals from (select rn,[v1],[v2],[v3],[v4] from cte)t
    unpivot (vals for id in([v1],[v2],[v3],[v4]))as unpvt)
    update @exmp_test
    set id = y.sumall
    from @exmp_test e
    join (
    select c.id,c.id + x.minvals as sumall,c.sumallversions, x.minvals from cte c 
    join(select rn,MIN(vals) minvals from unpvtcte group by rn)x on x.rn = c.rn) y
    on y.id = e.id
    and y.sumallversions = e.v1 + e.v2 + e.v3 + e.v4

Output:

id v1 v2 v3 v4
3 2 4 6 7
4 4 77 3 8
3 4 16 1 8

#1