英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论