blocks|key|3871|text|以下查询将为您提供所需的结果|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3872|select+tab.ID_CAR,+min(tab.val)+as+lowest_value+from
(
++++++(select+ID_CAR,min(col_1)+val
++++++from+table
++++++group+by+ID_CAR)
++union
++++++(select+ID_CAR,min(col_2)+val
++++++from+table
++++++group+by+ID_CAR)
++union
++++++(select+ID_CAR,min(col_3)+val
++++++from+table
++++++group+by+ID_CAR)
++union
++++++(select+ID_CAR,min(col_4)+val
++++++from+table
++++++group+by+ID_CAR)
++union
++++++(select+ID_CAR,min(col_5)+val
++++++from+table
++++++group+by+ID_CAR)
++union
++++++(select+ID_CAR,min(col_6)+val
++++++from+table
++++++group+by+ID_CAR)
)+tab
group+by+tab.ID_CAR|code-block|syntax|javascript|3873|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following query will give you the required result

<pre><code>select tab.ID_CAR, min(tab.val) as lowest_value from
(
 (select ID_CAR,min(col_1) val
 from table
 group by ID_CAR)
 union
 (select ID_CAR,min(col_2) val
 from table
 group by ID_CAR)
 union
 (select ID_CAR,min(col_3) val
 from table
 group by ID_CAR)
 union
 (select ID_CAR,min(col_4) val
 from table
 group by ID_CAR)
 union
 (select ID_CAR,min(col_5) val
 from table
 group by ID_CAR)
 union
 (select ID_CAR,min(col_6) val
 from table
 group by ID_CAR)
) tab
group by tab.ID_CAR
</code></pre>

blocks|key|4054|text|您需要UNION：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4055|select+id_car,+min(val)+as+lowest_value
from+(select+id_car,+col_1+as+Val
++++++from+table+union+
++++++select+id_car,+col_2
++++++from+table
++++++.+.+.
+++++)+t+
group+by+id_car;|code-block|syntax|javascript|4056|entityMap^0|3|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You need <code>UNION</code> :

<pre><code>select id_car, min(val) as lowest_value
from (select id_car, col_1 as Val
 from table union 
 select id_car, col_2
 from table
 . . .
 ) t 
group by id_car;
</code></pre>

blocks|key|2650056|text|没有union，您可以简单地组合least和min函数：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2650057|select
++ID_CAR,min(least(col_1,col_2,col_3,col_4,col_5,col_6))+lowest_value
from
++table
group+by
++ID_CAR|code-block|syntax|javascript|2650058|或者，如果您有null值，则需要ifnull或coalesce函数|2650059|select
++ID_CAR,
++min(least(
++++ifnull(col_1,~0),
++++ifnull(col_2,~0),
++++ifnull(col_3,~0),
++++ifnull(col_4,~0),
++++ifnull(col_5,~0),
++++ifnull(col_6,~0)
++))+as+lowest_value
from
++table
group+by
++ID_CAR|2650060|2650061|~0是mysql|unordered-list-item|2650062|中最大的bigint，least的对立面是greatest|2650063|The，min的对立面是max+;-)|2650064|2650065|适用于Mysql、Oracle、Postgres、Hive+...|2650066|Problem+2，类似于：|BOLD|2650067|select
++ID_CAR,
++min(least(
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_1,0)+DAY),
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_2,0)+DAY),
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_3,0)+DAY),
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_4,0)+DAY),
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_5,0)+DAY),
++++DATE_ADD(col_date,+INTERVAL+ifnull(col_6,0)+DAY)
++))+as+lowest_date
from
++table
group+by
++ID_CAR|2650068|或者如下所示(除非所有列都可以为空)：|2650069|select
++ID_CAR,
++DATE_ADD(col_date,+INTERVAL+min(least(
++++ifnull(col_1,~0),
++++ifnull(col_2,~0),
++++ifnull(col_3,~0),
++++ifnull(col_4,~0),
++++ifnull(col_5,~0),
++++ifnull(col_6,~0)
++))+DAY)+as+lowest_date
from
++table
group+by
++ID_CAR|2650070|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_least|1|https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_min^0|2|5|G|5|0|M|3|1|0|0|7|4|G|6|N|8|0|0|0|0|2|0|B|5|L|8|0|4|3|C|3|0|0|0|0|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@$9|1L|A|1M|1|1N]|$9|1O|A|1P|1|1Q]]|E|$]]|$1|F|3|G|5|H|7|1R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1S|8|@$9|1T|A|1U|B|C]|$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|20|8|@]|D|@]|E|$]]|$1|P|3|Q|5|R|7|21|8|@$9|22|A|23|B|C]]|D|@]|E|$]]|$1|S|3|T|5|R|7|24|8|@$9|25|A|26|B|C]|$9|27|A|28|B|C]]|D|@]|E|$]]|$1|U|3|V|5|R|7|29|8|@$9|2A|A|2B|B|C]|$9|2C|A|2D|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|2E|8|@]|D|@]|E|$]]|$1|X|3|Y|5|6|7|2F|8|@]|D|@]|E|$]]|$1|Z|3|10|5|6|7|2G|8|@$9|2H|A|2I|B|11]]|D|@]|E|$]]|$1|12|3|13|5|H|7|2J|8|@]|D|@]|E|$I|J]]|$1|14|3|15|5|6|7|2K|8|@]|D|@]|E|$]]|$1|16|3|17|5|H|7|2L|8|@]|D|@]|E|$I|J]]|$1|18|3|-4|5|6|7|2M|8|@]|D|@]|E|$]]]|19|$1A|$5|1B|1C|1D|E|$1E|1F]]|1G|$5|1B|1C|1D|E|$1E|1H]]]]

Without <code>union</code> you can simply combine <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_least" rel="nofollow noreferrer">least</a> and <a href="https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_min" rel="nofollow noreferrer">min</a> function :

<pre><code>select
 ID_CAR,min(least(col_1,col_2,col_3,col_4,col_5,col_6)) lowest_value
from
 table
group by
 ID_CAR
</code></pre>

or if you have <code>null</code>values you need <code>ifnull</code> or <code>coalesce</code> function

<pre><code>select
 ID_CAR,
 min(least(
 ifnull(col_1,~0),
 ifnull(col_2,~0),
 ifnull(col_3,~0),
 ifnull(col_4,~0),
 ifnull(col_5,~0),
 ifnull(col_6,~0)
 )) as lowest_value
from
 table
group by
 ID_CAR
</code></pre>

<ul>
<li><code>~0</code> is the max bigint in mysql</li>
<li>The opposite function of <code>least</code> is <code>greatest</code></li>
<li>The opposite function of <code>min</code> is <code>max</code> ;-)</li>
</ul>

Works with Mysql, Oracle, Postgres, Hive ...

Problem 2, something like this :

<pre><code>select
 ID_CAR,
 min(least(
 DATE_ADD(col_date, INTERVAL ifnull(col_1,0) DAY),
 DATE_ADD(col_date, INTERVAL ifnull(col_2,0) DAY),
 DATE_ADD(col_date, INTERVAL ifnull(col_3,0) DAY),
 DATE_ADD(col_date, INTERVAL ifnull(col_4,0) DAY),
 DATE_ADD(col_date, INTERVAL ifnull(col_5,0) DAY),
 DATE_ADD(col_date, INTERVAL ifnull(col_6,0) DAY)
 )) as lowest_date
from
 table
group by
 ID_CAR
</code></pre>

or like this (except if all columns can be null):

<pre><code>select
 ID_CAR,
 DATE_ADD(col_date, INTERVAL min(least(
 ifnull(col_1,~0),
 ifnull(col_2,~0),
 ifnull(col_3,~0),
 ifnull(col_4,~0),
 ifnull(col_5,~0),
 ifnull(col_6,~0)
 )) DAY) as lowest_date
from
 table
group by
 ID_CAR
</code></pre>

blocks|key|4600|text|如果您期望的值大于9999999999999999999，请尝试此操作，然后使用更高的值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4601|select+id_car,
min(least(coalesce(col_1,9999999999999999999),coalesce(col_2,9999999999999999999),coalesce(col_3,9999999999999999999),
++++++++++coalesce(col_4,9999999999999999999),coalesce(col_5,9999999999999999999),coalesce(col_6,9999999999999999999)
++++++++)
++++)+as+min_val+
from+your_table
group+by+id_car|code-block|syntax|javascript|4602|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this
If you are expecting values greater than 9999999999999999999, then use a higher values

<pre><code>select id_car,
min(least(coalesce(col_1,9999999999999999999),coalesce(col_2,9999999999999999999),coalesce(col_3,9999999999999999999),
 coalesce(col_4,9999999999999999999),coalesce(col_5,9999999999999999999),coalesce(col_6,9999999999999999999)
 )
 ) as min_val 
from your_table
group by id_car
</code></pre>

blocks|key|2440671|text|天真的方法是使用最少的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440672|SELECT+ID_CAR,+least(t.col_1,+t.col_2,+t.col_3,+t.col_4,+t.col_5,+t.col_6)
FROM
(SELECT+ID_CAR,+min(col_1)+as+col_1,+min(col_2)+as+col_2,+min(col_3)+as+col_3,+min(col_4)+as+col_4,+min(col_5)+as+col_5,+min(col_6)+as+col_6
FROM+YOUR_TABLE+GROUP+BY+ID_CAR)+t;|code-block|syntax|javascript|2440673|但是：如果最小的任何参数为NULL，它将返回NULL。您需要将NULL转换为一个较高的值(这是一个技巧，但在实践中会起作用，请参阅其他答案)。|offset|length|style|BOLD|2440674|这意味着要做这样的事情：|2440675|SELECT+ID_CAR,+LEAST(col_1,+col_2,+col_3,
+++++++++++++++++++++col_4,+col_5,+col_6)+as+l
FROM
(SELECT+ID_CAR,+
+++IFNULL(min(col_1),+9999)++as+col_1,+
+++IFNULL(min(col_2),+9999)++as+col_2,
+++IFNULL(min(col_3),+9999)++as+col_3,
+++IFNULL(min(col_4),+9999)++as+col_4,
+++IFNULL(min(col_5),+9999)++as+col_5,
+++IFNULL(min(col_6),+9999)++as+col_6
FROM+YOUR_TABLE+GROUP+BY+ID_CAR)+t;|2440676|但是，使用一个技巧将您的表转换为以下形式的三行表可能会更好：|2440677|car_id+%7C+attr+%7C+value
+++++1++++++1++++NULL+++;+or+use+strings+such+as+"size"
+++++1++++++2+++++250|2440678|entityMap^0|0|0|0|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

The naive approach would be using least:

<pre><code>SELECT ID_CAR, least(t.col_1, t.col_2, t.col_3, t.col_4, t.col_5, t.col_6)
FROM
(SELECT ID_CAR, min(col_1) as col_1, min(col_2) as col_2, min(col_3) as col_3, min(col_4) as col_4, min(col_5) as col_5, min(col_6) as col_6
FROM YOUR_TABLE GROUP BY ID_CAR) t;
</code></pre>

However: If ANY argument to LEAST is NULL, it'll return NULL. You'll either need to convert the NULLs to a high value (which is a hack but will work in practice, see other answers for this).

Which means doing something like this:

<pre><code>SELECT ID_CAR, LEAST(col_1, col_2, col_3,
 col_4, col_5, col_6) as l
FROM
(SELECT ID_CAR, 
 IFNULL(min(col_1), 9999) as col_1, 
 IFNULL(min(col_2), 9999) as col_2,
 IFNULL(min(col_3), 9999) as col_3,
 IFNULL(min(col_4), 9999) as col_4,
 IFNULL(min(col_5), 9999) as col_5,
 IFNULL(min(col_6), 9999) as col_6
FROM YOUR_TABLE GROUP BY ID_CAR) t;
</code></pre>

However, it might be good to use a trick to convert your table
into a three row table of the form:

<pre><code>car_id | attr | value
 1 1 NULL ; or use strings such as "size"
 1 2 250
</code></pre>

Problem 1

How can I get lowest value from table (not null), for ID_CAR? For example, for ID_CAR 1 lowest value is 50, for ID_CAR 2 lowest value is 50 and for ID_CAR 3 lowest value is 300. I don't need duplicates, I need only one value for one car.

<pre><code>ID_CAR | col_1 | col_2 | col_3 | col_4 | col_5 | col_6 

1 | null | 250 | 300 | null | 900 | null
2 | 100 | null | 300 | 600 | 200 | 100
1 | 300 | 100 | 800 | 100 | 50 | 900
3 | 300 | 4000 | null | null | null | null
2 | null | null | null | 50 | null | 100
4 | 400 | 900 | 500 | 700 | 800 | 500
</code></pre>

Problem 2
In this example, values in col_* are days. I need to add days to col_date and get lowest. For example lowest date for ID_CAR 1 is 2018-01-03 (col_2) and for ID_CAR 2 is 2018-01-15 (col_4). 

<pre><code>ID_CAR | col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_date

1 | null | 2 | 3 | null | 5 | null | 2018-01-01
2 | 1 | null | 3 | 6 | 10 | 10 | 2018-01-13
1 | 3 | 20 | 80 | 10 | 50 | 90 | 2018-01-02
3 | 30 | 40 | null | null | null | null | 2018-01-03
2 | null | null | null | 5 | null | 10 | 2018-01-10
4 | 10 | 9 | 5 | 70 | 8 | 50 | 2018-01-07
</code></pre>

How to get lowest value from table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

问题1对于ID_CAR，如何从表中获得最低值(非null)？例如，对于ID_CAR 1，最小值为50；对于ID_CAR 2，最小值为50；对于ID_CAR 3，最小值为300。我不需要重复，一辆车只需要一个值。ID_CAR | col_1 | col_2 | col_3 | col_4 | col_5 | col_6 ...

问如何从表中获取最低值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从表中获取最低值EN