列出在具有重复值的行之间列的最大值。

huangapple go评论57阅读模式
英文:

How to list the maximum value of a column in between rows with a value duplicated?

问题

我有一个有3列的表格。我想要创建一个SQL查询,以获取DP_ID = A之间的最大值。

ID DP_ID Value
1 A 10
2 B 264
3 B 265
4 B 266
5 A 10
6 B 115
7 B 116
8 A 25

期望的输出是:

ID DP_ID Value
4 B 266
7 B 116

我尝试创建一个子查询来根据具有DP_ID=A的行的ID来过滤表格。问题在于我一次只能检索一个实例。我期望的输出将列出所有它的出现。

英文:

I have a table with 3 columns. I want make a SQL query that results in the maximum value in between the occurrences of DP_ID = A

ID DP_ID Value
1 A 10
2 B 264
3 B 265
4 B 266
5 A 10
6 B 115
7 B 116
8 A 25

The desired output would be:

ID DP_ID Value
4 B 266
7 B 116

I've tried to make a subquery to filter the table based on the ID of the rows that have DP_ID=A. The problem is that I can only retrieve one instance bye time. My desired output would list all the occurrences of it

答案1

得分: 0

Apache Derby 是一个相当有限的数据库,不支持窗口函数或公共表表达式(CTE)。

注意:我强烈建议您切换到 H2,它具有类似的占用空间,但提供了更好的功能。

在 Derby 中,查询变得冗长且性能不佳,但可以写成:

select *
from (
  select
    t.*, 
    (select sum(case when dp_id = 'A' then 1 else 0 end) from t x
     where x.id <= t.id) as g
  from t
) z
where dp_id <> 'A'
  and value = (
  select max(value) as mv
  from (
    select
      t.*, 
      (select sum(case when dp_id = 'A' then 1 else 0 end) from t x 
       where x.id <= t.id) as g
    from t
  ) y
  where dp_id <> 'a' and y.g = z.g
  )

结果:

ID  DP_ID  VALUE  G
--  -----  -----  -
 4  B        266  1
 7  B        116  2

展示此示例的数据脚本为:

create table t(
   id    integer  not null
  ,dp_id varchar(2) not null
  ,value integer  not null
);

insert into t(id,dp_id,value) values (1,'A',10);
insert into t(id,dp_id,value) values (2,'B',264);
insert into t(id,dp_id,value) values (3,'B',265);
insert into t(id,dp_id,value) values (4,'B',266);
insert into t(id,dp_id,value) values (5,'A',10);
insert into t(id,dp_id,value) values (6,'B',115);
insert into t(id,dp_id,value) values (7,'B',116);
insert into t(id,dp_id,value) values (8,'A',25);
英文:

Apache Derby is a quite limited database that does not support window functions or CTEs.

Note: I would strongly suggest you switch to H2 that has a similar footprint and offers much better capabilities.

The query becomes tediously long and non-performant in Derby, but can be written as:

select *
from (
  select
    t.*, 
    (select sum(case when dp_id = 'A' then 1 else 0 end) from t x
     where x.id <= t.id) as g
  from t
) z
where dp_id <> 'A'
  and value = (
  select max(value) as mv
  from (
    select
      t.*, 
      (select sum(case when dp_id = 'A' then 1 else 0 end) from t x 
       where x.id <= t.id) as g
    from t
  ) y
  where dp_id <> 'a' and y.g = z.g
  )

Result:

ID  DP_ID  VALUE  G
--  -----  -----  -
 4  B        266  1
 7  B        116  2

The data script that demonstrates this example is:

create table t(
   id    integer  not null
  ,dp_id varchar(2) not null
  ,value integer  not null
);

insert into t(id,dp_id,value) values (1,'A',10);
insert into t(id,dp_id,value) values (2,'B',264);
insert into t(id,dp_id,value) values (3,'B',265);
insert into t(id,dp_id,value) values (4,'B',266);
insert into t(id,dp_id,value) values (5,'A',10);
insert into t(id,dp_id,value) values (6,'B',115);
insert into t(id,dp_id,value) values (7,'B',116);
insert into t(id,dp_id,value) values (8,'A',25);

huangapple
  • 本文由 发表于 2023年6月15日 04:01:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76477161.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定