select * from t t3 where not existis(select 1 from (select distinct name1 nm1 from t where t.cost1<450) t2 where t3.name1=t2.nm1)SELECT T2.* FROM (SELECT MIN(COST1) cost1, month1 FROM (SELECT name1, month1, SUM(COST1) COST1 FROM T GROUP BY T.name1, T.month1) GROUP BY month1) T1, (SELECT name1, month1, SUM(COST1) COST1 FROM T GROUP BY T.name1, T.month1) T2 WHERE T1.COST1 = T2.COST1 AND T1.month1= T2.month1;
[code=sql][with t as( select 'A' name1,'05' month1,500 cost1 from dual union all select 'B' ,'06' ,450 from dual union all select 'A' ,'06' ,450 from dual union all select 'B' ,'05' ,430 from dual union all select 'C' ,'05' ,460 from dual union all select 'C' ,'06' ,475 from dual ) select name1,min(cost1) from(select name1,min(cost1) cost1 from t group by name1,cost1 having cost1>=450)t1 group by name1/code]
FROM (SELECT MIN(COST1) cost1, month1
FROM (SELECT name1, month1, SUM(COST1) COST1 FROM T GROUP BY T.name1, T.month1)
GROUP BY month1) T1,
(SELECT name1, month1, SUM(COST1) COST1 FROM T GROUP BY T.name1, T.month1) T2
WHERE T1.COST1 = T2.COST1
AND T1.month1= T2.month1;
select 'A' name1,'05' month1,500 cost1 from dual union all
select 'B' ,'06' ,450 from dual union all
select 'A' ,'06' ,450 from dual union all
select 'B' ,'05' ,430 from dual union all
select 'C' ,'05' ,460 from dual union all
select 'C' ,'06' ,475 from dual
)
select name1,min(cost1)
from(select name1,min(cost1) cost1
from t
group by name1,cost1
having cost1>=450)t1
group by name1/code]