sql中同事满足两个条件的，求助

[田哥coder]

1，查出每个name1并且每个月的cost1都是大于等于450的，2，查出每个月cost1最少的name1,

[一颗向往数据的心丶]

每月大于等于 可以用min（cost1实现。 每个月cost1最少（是值总数还是？）总数的话sum(cost1)。

[田哥coder]

引用 1 楼 z_shousi 的回复:

每月大于等于 可以用min（cost1实现。 每个月cost1最少（是值总数还是？）总数的话sum(cost1)。

比如说05月哪一个name1的cost1最少，但是要把06的也要一起查出

[CT_LXL]

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;

[田哥coder]

引用 3 楼 zlloct 的回复:

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;

谢谢了

[crystalis]

[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]