oracle - max 带案例

发布于 2024-10-20 19:40:19 字数 1323 浏览 3 评论 0原文

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

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

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

谢谢！

更新：这是整个声明

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);

原文

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".

This select statement is part of a larger insert.

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);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深海里的那抹蓝 2024-10-27 19:40:19

max 聚合函数将忽略 null 值，因此您不需要 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

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.

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

回复收藏 0 原文

A君 2024-10-27 19:40:19

我认为您想要：（

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

或者如果您愿意，您可以使用 Oracle 的 nvl 而不是 ANSI 的 coalesce）。

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 nvl instead of ANSI's coalesce if you prefer).

回复收藏 0 原文

渡你暖光 2024-10-27 19:40:19

试试这个：

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

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

回复收藏 0 原文

御守 2024-10-27 19:40:19

编辑：

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

尝试...

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

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

但 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

Edit :

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

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.

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

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

回复收藏 0 原文

~没有更多了~