用case when 判断就好了select * from ZB_SAL_TB where (case when (SAL_1>500) then 1 else 0 end)+ (case when (SAL_2>500) then 1 else 0 end)+ (case when (SAL_3>500) then 1 else 0 end)+ (case when (SAL_4>500) then 1 else 0 end)+ (case when (SAL_5>500) then 1 else 0 end)+ (case when (SAL_6>500) then 1 else 0 end)+ (case when (SAL_7>500) then 1 else 0 end)+ (case when (SAL_8>500) then 1 else 0 end)+ (case when (SAL_9>500) then 1 else 0 end)+ (case when (SAL_10>500) then 1 else 0 end)+ (case when (SAL_11>500) then 1 else 0 end)+ (case when (SAL_12>500) then 1 else 0 end) >=2
取多列的最大值用greatest函数,如果不行你就得做行列转化后再查了
SELECT * FROM ZB_SAL_TB WHERE decode(sign( SAL_1-500),1,1)+ decode(sign( SAL_2-500),1,1)+decode(sign( SAL_3-500),1,1)+ decode(sign( SAL_4-500),1,1)+ decode(sign( SAL_5-500),1,1)+decode(sign( SAL_6-500),1,1)+ .....>2
用case when 判断就好了select * from ZB_SAL_TB
where (case when (SAL_1>500) then 1 else 0 end)+
(case when (SAL_2>500) then 1 else 0 end)+
(case when (SAL_3>500) then 1 else 0 end)+
(case when (SAL_4>500) then 1 else 0 end)+
(case when (SAL_5>500) then 1 else 0 end)+
(case when (SAL_6>500) then 1 else 0 end)+
(case when (SAL_7>500) then 1 else 0 end)+
(case when (SAL_8>500) then 1 else 0 end)+
(case when (SAL_9>500) then 1 else 0 end)+
(case when (SAL_10>500) then 1 else 0 end)+
(case when (SAL_11>500) then 1 else 0 end)+
(case when (SAL_12>500) then 1 else 0 end) >=2
可以把表设计成类似于 id,月份(yyyymm),工资. 三个字段,会清晰些.id,月份为主键
这个的效率会不会比较高呢`~?