提问人：William Piah 提问时间：8/21/2023 更新时间：8/31/2023 访问量：49
如果有 NULL ELSE 显示 MAX 值，我该如何显示 NULL
How can I show NULL if There is NULL ELSE Show MAX value
问：
我有一个表格，上面有一些出勤数据。 如果没有 NULL，我正在尝试制定一个具有不同用户的表，该表具有开始 （MIN） 和结束 （MAX）。
我有“time_captured”的表
| 用户 | 开始 | 结束 |
|---|---|---|
| 一个 | 0 | 60 |
| 一个 | 60 | 120 |
| 一个 | 700 | |
| C | 120 | 180 |
| C | 540 | 860 |
我想要的结果
| 用户 | 开始 | 结束 |
|---|---|---|
| 一个 | 0 | |
| C | 120 | 860 |
我尝试了以下代码，但结果不是我预期的（显示原始表）
SELECT
MIN(time_captured.start_time),
CASE
WHEN
time_captured.end_time IS NULL THEN NULL
ELSE
MAX(time_captured.end_time)
END,
time_captured.user_sign
FROM
time_captured
GROUP BY
time_captured.user_sign,
time_captured.end_time
| 用户 | 开始 | 结束 |
|---|---|---|
| 一个 | 0 | 60 |
| 一个 | 60 | 120 |
| 一个 | 700 | |
| C | 120 | 180 |
| C | 540 | 860 |
答：
5赞
jarlh
8/21/2023
#1
如果 和 相同，则没有 NULL 值，即返回值。（否则返回 NULL。COUNT(*)COUNT(time_captured.end_time)MAX()
SELECT time_captured.user_sign
MIN(time_captured.start_time),
CASE WHEN COUNT(*) = COUNT(time_captured.end_time)
THEN MAX(time_captured.end_time)
END
FROM time_captured
GROUP BY time_captured.user_sign
评论
1赞
Tim Schmelter
8/21/2023
按预期工作：sqlfiddle.com/#!18/9df49/13
1赞
Littlefoot
8/21/2023
#2
另一种选择是使用条件聚合;计算 中的值数 ;如果它等于 ，则没有空值，因此返回最大值。null[End]0
select
user_sign,
min([Start]) c_start,
case when sum(case when [End] is null then 1 else 0 end) = 0 then max([End])
end c_end
from time_captured
group by user_sign;
0赞
Florin
8/31/2023
#3
我使用 first_value 和 last_value 提供在 Sql Server 2022 -last 2 和 Oracle 21c -first 2 中解决的解决方案：
with x as
(select p.useru,
min(p.startu) over (partition by p.useru) as minim,
last_value(p.endu) over (partition by p.useru order by (select 1 from dual) nulls last) as maxim2
from your_table p)
select distinct xx.useru, xx.minim, xx.maxim2
from x xx;
with x as
(select p.useru,
min(p.startu) over (partition by p.useru) as minim,
first_value(p.endu) over (partition by p.useru order by p.endu desc nulls first) as maxim2
from your_table p)
select distinct xx.useru, xx.minim, xx.maxim2
from x xx;
with x as
(select p.useru,
min(p.startu) over (partition by p.useru) as minim,
last_value(p.endu) over (partition by p.useru order by case when (select 1 ) is null then 1 else 0 end ) as maxim2
from your_table p)
select distinct xx.useru, xx.minim, xx.maxim2
from x xx;
with x as
(select p.useru,
min(p.startu) over (partition by p.useru) as minim,
first_value(p.endu) over (partition by p.useru order by case when p.endu is null then 0 else 1 end
, p.endu desc ) as maxim2
from your_table p)
select distinct xx.useru, xx.minim, xx.maxim2
from x xx;
希望它有所帮助。
评论