如图:
数据库是oracle 开发端是PBSQL:
select code,
rqsj,
type,
round(num1,2) num1,
round(sum(decode(sign(num1 - 200),-1,num1,null)),2) "200分以下",
round(sum(decode(sign(num1 - 199) + sign(num1 - 501),0,num1,null)),2) "200-500",
round(sum(decode(sign(num1 - 500) + sign(num1 - 801),0,num1,null)),2) "500-800",
round(sum(decode(sign(num1 - 800),1,num1,null)),2) "800以上 from CRD_CARDLOG
WHERE CRD_CARDLOG.zy IN ('J','B') AND
CRD_CARDLOG.type in ('2','6') and
CRD_CARDLOG.RQSJ>=:V_KSRQ AND
CRD_CARDLOG.RQSJ< :V_JSRQ +1 AND
CRD_CARDLOG.CODE LIKE :V_KH
GROUP BY code,
rqsj,
type,
num1
ORDER BY CRD_CARDLOG.RQSJ DESC我想按等级进行查询,查询条件有个下拉,是200分以下,200-500,500-800,800以上的,,请问我这个SQL的条件如何写
数据库是oracle 开发端是PBSQL:
select code,
rqsj,
type,
round(num1,2) num1,
round(sum(decode(sign(num1 - 200),-1,num1,null)),2) "200分以下",
round(sum(decode(sign(num1 - 199) + sign(num1 - 501),0,num1,null)),2) "200-500",
round(sum(decode(sign(num1 - 500) + sign(num1 - 801),0,num1,null)),2) "500-800",
round(sum(decode(sign(num1 - 800),1,num1,null)),2) "800以上 from CRD_CARDLOG
WHERE CRD_CARDLOG.zy IN ('J','B') AND
CRD_CARDLOG.type in ('2','6') and
CRD_CARDLOG.RQSJ>=:V_KSRQ AND
CRD_CARDLOG.RQSJ< :V_JSRQ +1 AND
CRD_CARDLOG.CODE LIKE :V_KH
GROUP BY code,
rqsj,
type,
num1
ORDER BY CRD_CARDLOG.RQSJ DESC我想按等级进行查询,查询条件有个下拉,是200分以下,200-500,500-800,800以上的,,请问我这个SQL的条件如何写
and
(
(:jf=1 and num1<200) or (:jf=2 and num1>=200 and num1<500)
or
(:jf=3 and num1>=500 and num1<800)
or
(:jf=4 and num1>=800) )