oracle语句 我想知道在一个字段最大值的情况下, 取另外一个字段最小值的所在记录
字段 Group Item C1 C2 C3 C4
值 A 1 10 1 5 7
A 1 5 2 3 1
A 1 10 3 4 5
A 2 6 5 3 3
A 2 13 1 2 6
B 1 1 2 4 5
通过 Group,Item 分组 取出C1最大值情况下的 C4最小值的记录
结果应该是
A 1 10 3 4 5
A 2 13 1 2 6
B 1 1 2 4 5
我现在用的是多层的Select 嵌套,想知道大家是怎么写的
谢谢
最新回答
素颜繁华梦
2024-10-24 07:50:33
-- group 是关键字，最好不要用于列名
select t1.* from t1, (select t1.fgroup, t1.fitem, max(t1.c1) c1, min(t1.c4) c4
from T1, (select fgroup, fitem, max(c1) c1
from T1
group by fgroup, fitem) maxc1
where t1.fgroup = maxc1.fgroup
and t1.fitem = maxc1.fitem
and t1.c1 = maxc1.c1
group by t1.fgroup, t1.fitem) minc4
where t1.fgroup = minc4.fgroup
and t1.fitem = minc4.fitem
and t1.c1 = minc4.c1
and t1.c4 = minc4.c4;
在等一个康复日期
2024-10-24 07:51:37
select * from t1 t where exists
(select 1 from
(select a.group,a.item,a.c1,min(a.c4) c4 from t1 as a,
(select group,item,max(c1) as c1 from t1 group by group,item) as b
where a.group=b.group
and a.item=b.item
and a.c1=b.c1
group by a.group,a.item,a.c1) c
where t.group=c.group
and t.item=c.item
and t.c1=c.c1
and t.c4=c.c4)
巷陌
2024-10-24 07:59:07
栀夏暖阳
2024-10-24 07:41:43
热门标签
