如何在多个col中找到Min值

i have in my DB 3 col and i want to find a single value among all of them as explaind here: table name:MyTable

我在我的DB 3 col中,我想在所有这些中找到一个值作为explainind:table name:MyTable

-----------------------------
--id-- col1-- col2-- col3-
-----------------------------
   1     200    300     400 
   2     100    150     300
   3     800    102     20
   4     80     80       0

i want the result out of col1 col2 col3 to be = 0 , which is the min value among them. is it possible !!!!

我希望col1 col2 col3的结果为= 0,这是它们中的最小值。可能吗 !!!!

my try:

select Min(col1, col2 , col3) as Tablemin
from MyTable

4 个解决方案

#1

ANSI SQL:

select min(col)
from
(
    select col1 [col] from MyTable
    union all
    select col2 from MyTable
    union all
    select col3 from MyTable
)t

#2

If it is MySQL or Oracle, there is LEAST() function:

如果是MySQL或Oracle,则有LEAST()函数:

SELECT LEAST(MIN(col1), MIN(col2), MIN(col3))
       AS MinOfAllColumns
FROM MyTable

Then, there is the CASE statement which can be used in most RDBMSs:

然后,有一个CASE语句可以在大多数RDBMS中使用:

SELECT CASE WHEN MIN(col1) <= MIN(col2) AND MIN(col1) <= MIN(col3) 
                THEN MIN(col1)
            WHEN MIN(col2) <= MIN(col3)
                THEN MIN(col2)
                ELSE MIN(col3)            
       END 
       AS MinOfAllColumns
FROM MyTable

This can also work but it's hard to get other fields with the UNION approach:

这也可以工作,但很难通过UNION方法获得其他字段:

SELECT MIN(col) AS MinOfAllColumns
FROM 
  ( SELECT MIN(col1) AS col
    FROM MyTable
  UNION
    SELECT MIN(col2)
    FROM MyTable
  UNION
    SELECT MIN(col3)
    FROM MyTable
  ) AS tmp

#3

Simple way to display min is:

显示min的简单方法是:

string val = textBox2.Text;
DataSet ds;
con.Open();
string str = "SELECT * FROM Add_Rooms WHERE Price >= @price";
cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue("@price", val);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Add_Rooms");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Add_Rooms";
con.Close();

#4

In SQLITE the answer is as simple as:

在SQLITE中,答案很简单:

SELECT MIN(MIN(col1), MIN(col2), MIN(col3)) FROM MyTable;

#1