SQL查询:查找最多2个数字(共3个)并将其存储在另一列中 [英] SQL Query : Find max 2 of 3 number and store it in another column

问题描述

我有一个名为result的表，其中有列name, rollno, sub1, sub2, sub3, max1, max2等. sub1, sub2, sub3将存储获得的标记.我想找到sub1, sub2, sub3的最大值并将其存储在max1中，并找到sub1, sub2, sub3的第二最大值并将其存储在max2中.

I have a table named result having column name, rollno, sub1, sub2, sub3, max1, max2 etc. sub1, sub2, sub3 will store marks obtained. I want to find max of sub1, sub2, sub3 and store it in max1 and find second max of sub1, sub2, sub3 and store it in max2.

示例

sub1  sub2  sub3      max1  max2
10     15    20        20    15
40     10    25        40    25
33     64    51        64    51

有人可以告诉我这个的sql代码吗?

Can anyone tell me the sql code for this ?

更新

我不想将最大数目和第二个最大数目存储到max1，而是将最大数目和第二个最大数目除以2，并希望将其存储在average列中而不将其存储在max1中和max2.

Instead of storing the maximum number to max1 and second maximum to max2, I want to divide maximum number and second maximum number by 2 and want to store it in average column without storing it in max1 and max2.

意味着我不希望两个额外的列max1和max2存储最大值和第二个最大值，然后将其相加并除以2，然后将其存储在average中.我想做平均测验.

Means I don't want two extra column max1 and max2 to store maximum and second maximum, then add it and divide it by 2 then store it in average. I want to do average direclty.

请更新代码.

示例

sub1  sub2  sub3   average

10     15    20     17.5   ( (Maximum + Second Maximum)/2 )
40     10    25     32.5   
33     64    51     57.5  

推荐答案

请考虑以下内容

mysql> create table test (sub1 int, sub2 int , sub3 int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values (20,30,40),(10,40,50),(30,10,20);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+------+------+------+
| sub1 | sub2 | sub3 |
+------+------+------+
|   20 |   30 |   40 |
|   10 |   40 |   50 |
|   30 |   10 |   20 |
+------+------+------+

因此要从列中获取max1和max2，可以使用greatest函数.

So to get the max1 and max2 from the columns you can use greatest function.

select * ,
greatest(sub1,sub2,sub3) as max1 , 
greatest(
 case 
  when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
 end,
 case 
  when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
 end, 
 case 
  when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
 end
) as max2 from test ;

这将为您提供

+------+------+------+------+------+
| sub1 | sub2 | sub3 | max1 | max2 |
+------+------+------+------+------+
|   20 |   30 |   40 |   40 |   30 |
|   10 |   40 |   50 |   50 |   40 |
|   30 |   10 |   20 |   30 |   20 |
+------+------+------+------+------+

您可以将其用作更新命令

You can use this for update command as

update table_name
set 
max1 = greatest(sub1,sub2,sub3),
max2 = greatest(
 case 
  when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
 end,
 case 
  when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
 end, 
 case 
  when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
 end
) 

获取max1和max2的平均值并更新为

To get the average of max1 and max2 and update as

update table_name
set 
`average`
= ( 
   greatest(sub1,sub2,sub3)+
   greatest(
    case 
      when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
    end,
    case 
      when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
    end, 
    case 
      when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
    end
     )
)/2 ; 

这篇关于SQL查询:查找最多2个数字(共3个)并将其存储在另一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！