mysql - 如何在mysql中获取连续第二最少的列

Question

我在一个表中有大约 8 列。所有列都是相同类型的整数。

|id  | marks_1 | marks_2 | marks_3|....|marks_7|
|1001|0        |20       |15      |....|25     |
|1002|20       |0        |11      |....|0      |
.
.
.
.
|1010|40       |0        |0       |....|25     |

我想得到输出为

|id  |marks   |
|1001|15      |
|1002|11      |

我尝试使用 MySql 的 LEAST() 函数获取这些列中最少的列。

SELECT id, LEAST(marks_1,marks_2,marks_3,marks_4,marks_5,marks_6,marks_7) AS marks from tbl_name

它正在运行。

它吸引了我

|id  |marks   |
|1001|0       |
|1002|0       |

但我需要第二个。即行中的非零记录。

Answer 1

如果您想要的是大于 0 的最小数字，那么您可以逆透视您的表并对其应用分组:

SELECT id, MIN(c)
FROM (
   SELECT id, 
          CASE t2.n
             WHEN 1 THEN col_1
             WHEN 2 THEN col_2
             WHEN 3 THEN col_3
             WHEN 4 THEN col_4
             WHEN 5 THEN col_5
             WHEN 6 THEN col_6
             WHEN 7 THEN col_7
          END AS c
   FROM mytable AS t1
   CROSS JOIN (
      SELECT 1 AS n UNION ALL
      SELECT 2 UNION ALL
      SELECT 3 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5 UNION ALL
      SELECT 6 UNION ALL
     SELECT 7) AS t2) AS x
WHERE x.c > 0   
GROUP BY id   

Demo here