with t as ( select 4 as a, 2 as b, 30 as c from dual union all select 5 as a, 1 as b, 76 as c from dual union all select 5 as a, 2 as b, 87 as c from dual union all select 6 as a, 1 as b, 31 as c from dual ) select t1.* from t t1, t t2 where t1.b = 1 and t2.b = 2 and t1.c > t2.c; A B C ---------- ---------- ---------- 5 1 76 6 1 31
select builidid,max(projectname),sum(buildare),sum(buildvalue), case when count(1)<30 then 1 else 0 end as 套数小于30, case when count(1)>=30 and count(1)<=40 then 1 else 0 end) as 套数30至40, case when count(1)>40then 1 else 0 end as 套数大于40,sum(case when buildare<30 then 1 else 0 end) as 面积小于30, sum(case when buildare>=30 and zmianji<=40 then 1 else 0 end) as 面积30至40, sum(case when buildare>40then 1 else 0 end) as 面积大于40 ,sum(case when buildvalue<30 then 1 else 0 end) as 金额小于30, sum(case when buildvalue>=30 and zjine<=40 then 1 else 0 end) as 金额30至40, sum(case when buildvalue>40then 1 else 0 end) as 金额大于40 from table1 group by builidid
是不是可以这样理解,B=1时C为{76,31},B=2时,C为{30,78},于是就有如下SQL语句:select * from tab where b=1 and c> ((select max(c) from tab where b=2) b2);
with t as ( select 4 as a, 2 as b, 30 as c from dual union all select 5 as a, 1 as b, 76 as c from dual union all select 5 as a, 2 as b, 87 as c from dual union all select 6 as a, 1 as b, 31 as c from dual ) select t1.* from t t1, t t2 where t1.b = 1 and t2.b = 2 and t1.c > t2.c;相当于做两个子表
是只要大于任意 B=2 的 C 值即可吗?
(
select 4 as a, 2 as b, 30 as c from dual
union all
select 5 as a, 1 as b, 76 as c from dual
union all
select 5 as a, 2 as b, 87 as c from dual
union all
select 6 as a, 1 as b, 31 as c from dual
)
select t1.*
from t t1, t t2
where t1.b = 1
and t2.b = 2
and t1.c > t2.c; A B C
---------- ---------- ----------
5 1 76
6 1 31
B=1时C的值有 76 和 31
B=2时C的值有 30 和 87
你想让谁和谁比较呢? 比如:是最大值比最小值还是其他 你得有个规则呀。
光比较(B=1时的C值)>(B=2时的C值) 这个 没意义吧
case when count(1)<30 then 1 else 0 end as 套数小于30,
case when count(1)>=30 and count(1)<=40 then 1 else 0 end) as 套数30至40,
case when count(1)>40then 1 else 0 end as 套数大于40,sum(case when buildare<30 then 1 else 0 end) as 面积小于30,
sum(case when buildare>=30 and zmianji<=40 then 1 else 0 end) as 面积30至40,
sum(case when buildare>40then 1 else 0 end) as 面积大于40 ,sum(case when buildvalue<30 then 1 else 0 end) as 金额小于30,
sum(case when buildvalue>=30 and zjine<=40 then 1 else 0 end) as 金额30至40,
sum(case when buildvalue>40then 1 else 0 end) as 金额大于40
from table1 group by builidid
(
select 4 as a, 2 as b, 30 as c from dual
union all
select 5 as a, 1 as b, 76 as c from dual
union all
select 5 as a, 2 as b, 87 as c from dual
union all
select 6 as a, 1 as b, 31 as c from dual
)
select t1.*
from t t1, t t2
where t1.b = 1
and t2.b = 2
and t1.c > t2.c;相当于做两个子表
C2是B取2时,C列的值