oracle - 最大与案例

oracle - max with case

提问by kralco626

select case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else max(cntrctr_lcns_seq_no)
                                   end as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id

I think you an see what i'm trying to do. Get the max seq_no for a specific id. But I get the error "not a single group group clause".

我想你明白我在做什么。获取特定 id 的最大 seq_no。但是我收到错误“不是单个组组子句”。

This select statement is part of a larger insert.

此 select 语句是较大插入的一部分。

Thanks!

谢谢！

update: this is the whole statement

更新：这是整个声明

insert into nuwmsweb.CNTRCTR_LCNS_INFO
    (third_party_id,cntrctr_lcns_seq_no,cntrctr_lcns_no,
    lcns_st_cd,certfn_level_type_cd,cntrctr_type_cd,cut_tap_authy_ind,
    stat_type_nm)
    VALUES(thirdPartyId,(select max(case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else cntrctr_lcns_seq_no
                                   end) as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id
                                    ),
          licenseNumber,licenseState,licenseLevel,licenseType,cutTap,status);

回答by a'r

The max aggregate function will ignore null values, so you don't need the case statement or the group by as you want the max over the entire returned set.

max 聚合函数将忽略空值，因此您不需要 case 语句或 group by，因为您需要整个返回集的最大值。

select
    coalesce(max(cntrctr_lcns_seq_no), 1) as cntrctr_lcns_seq_no
from nuwmsweb.cntrctr_lcns_info
where third_party_id = thirdPartyId

回答by Tony Andrews

I think you want:

我想你想要：

select coalesce (max(cntrctr_lcns_seq_no),1) as cntrctr_lcns_seq_no
  from nuwmsweb.cntrctr_lcns_info
 where third_party_id = thirdPartyId

(or you can use Oracle's nvlinstead of ANSI's coalesceif you prefer).

（或者nvl，coalesce如果您愿意，可以使用 Oracle代替 ANSI ）。

回答by Lamak

Try this:

尝试这个：

select  max(case when cntrctr_lcns_seq_no is null then 1
        else cntrctr_lcns_seq_no end) as cntrctr_lcns_seq_no
from nuwmsweb.cntrctr_lcns_info
where third_party_id = thirdPartyId                                     
group by third_party_id 

回答by Rajesh Chamarthi

Edit :

编辑 ：

The error you are getting is because you are not including the third_party_id in the group by clause.

您收到的错误是因为您没有在 group by 子句中包含 third_party_id。

Try...

尝试...

select max(case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else cntrctr_lcns_seq_no
                                   end) as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id

If you really want to use this syntax for a different (more complicated query). Also, you should add the third_party_id in the group by clause.

如果您真的想将此语法用于不同的（更复杂的查询）。此外，您应该在 group by 子句中添加third_party_id。

But max already ignores nulls, so wouldn't a query like this be more descriptive of what you are trying to do?

但是 max 已经忽略了空值，所以像这样的查询不是更能描述你想要做什么吗？

select third_party_id, max(nvl(cntrctr_lcns_seq_no,1))
        from nuwmsweb.cntrctr_lcns_info
        where third_party_id = thirdPartyId
        group by third_party_id