获取多列中的最小非零值 [英] Get the minimum non zero value across multiple columns

问题描述

假设我有下表：

CREATE TABLE numbers
(
  key integer NOT NULL DEFAULT 0,
  number1 integer NOT NULL DEFAULT 0,
  number2 integer NOT NULL DEFAULT 0,
  number3 integer NOT NULL DEFAULT 0,
  number4 integer NOT NULL DEFAULT 0,

  CONSTRAINT pk PRIMARY KEY (key),
  CONSTRAINT nonzero CHECK (key <> 0)
)

我要检索的是所有4个数字中给定键的最小值，但忽略了那些零。

What I want to retrieve is the minimum value from a given key of all 4 numbers, but ignoring those that are zero.

当我发现自己的零点会出现问题时，我就是这样开始的：

I started with something like this when I figured that I'll have problem with the zeros:

SELECT LEAST(number1, number2, number3, number4) FROM numbers WHERE key = 1

例如，如果我有元组（1、5、0、3、2）我想返回 2 ，或者返回（2，3，0，0，0）我想返回 3 ，依此类推。

For instance, if I have the tuple (1, 5, 0, 3, 2) I want to return 2, or for (2, 3, 0, 0, 0) I want to return 3 and so on.

可以是在单个查询（或嵌套）中完成的，我真的不想写一个程序来做到这一点。

Can this be done in a single query (or maybe nested), I don't really want to have to write a procedure to do this.

推荐答案

尝试NULLIF函数

SELECT LEAST(
        nullif( number1, 0 ), 
        nullif( number2, 0 ), 
        nullif( number3, 0 ), 
        nullif( number4, 0 )) 
FROM numbers

演示-> http：/ /www.sqlfiddle.com/#!12/641fb3/1

这篇关于获取多列中的最小非零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！