如何从表中获取最低值
如何从表中获取最低值
提问于 2018-06-15 21:18:41
问题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
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问题2在此示例中，col_*中的值是天。我需要将天数添加到col_date并得到最低天数。例如，ID_CAR 1的最低日期是2018-01-03 (col_2)，ID_CAR 2的最低日期是2018-01-15 (col_4)。
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回答 5
Stack Overflow用户
回答已采纳
发布于 2018-06-15 21:34:35
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或者，如果您有null值，则需要ifnull或coalesce函数
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~0是mysql- 中最大的bigint，
least的对立面是greatest - The，
min的对立面是max;-)
适用于Mysql、Oracle、Postgres、Hive ...
Problem 2，类似于：
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或者如下所示(除非所有列都可以为空)：
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_CARStack Overflow用户
发布于 2018-06-15 21:22:59
以下查询将为您提供所需的结果
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_CARStack Overflow用户
发布于 2018-06-15 21:23:10
您需要UNION：
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50876464
复制相关文章
相似问题
腾讯云开发者
