Question

Ibatis如何查询多项统计信息

举个表的例子， 表class（classId, studentId, studentName, avgScore）
使用oracle数据库，
已知classId，用一条sql语句去查询该班级的学生总数，平均分大于90的学生数，平均分60-90的学生数和平均分小于60的学生数
在sqlmap.xml里应该怎么去写？
如果还要附加一个及格率的话，怎么写？

Answer 1

select
count(id) as num,
ym as certificationDateStr,
area_id as areaId
from (select t.area_id, t.id, to_char(t.certification_date, 'yyyy-MM') ym
from spcy_license t
<dynamic prepend="WHERE">
<isNotEmpty prepend="and" property="chargePersonType">
t.CHARGE_PERSON_TYPE = #chargePersonType# </isNotEmpty>
<isNotEmpty prepend="and" property="certificationDate">
t.CERTIFICATION_DATE between #certificationDate# and #endCertificationDate# </isNotEmpty>
<isNotEmpty prepend="and" property="chargePersonName">
t.CHARGE_PERSON_NAME like '%$chargePersonName$%' </isNotEmpty>
<isNotEmpty prepend="and" property="permitNo">
t.PERMIT_NO = #permitNo# </isNotEmpty>
<isNotEmpty prepend="and" property="foodSafetyAdmin">
t.FOOD_SAFETY_ADMIN like '%$foodSafetyAdmin$%' </isNotEmpty>
<isNotEmpty prepend="and" property="economyNature">
t.ECONOMY_NATURE = #economyNature# </isNotEmpty>
<isNotEmpty prepend="and" property="companyName">
t.COMPANY_NAME like '%$companyName$%' </isNotEmpty>
<isNotEmpty prepend="and" property="licenseType">
t.LICENSE_TYPE = #licenseType# </isNotEmpty>
<isNotEmpty prepend="and" property="areaId">
t.AREA_ID like '$areaId$%' </isNotEmpty>
<isNotEmpty prepend="and" property="status">
t.STATUS = #status# </isNotEmpty>
</dynamic>
)
group by ym, area_id order by certificationDateStr desc
</select>
你可以参考下这个，这个是个统计查询的ibatIS sqlMap

Answer 2

select classid,sum(case when avgscore<60 then 1 else 0 end )as failure,
sum(case when (avgscore >= 60 and avgscore<90) then 1 else 0 end )as pass,
sum(case when avgscore > 90 then 1 else 0 end )as highgrade,
sum(case when (avgscore >= 60) then 1 else 0 end )/count(*)*100 || '%' as passrate
from class
group by classid;

上面的语句，用case when 判断，然后sum()统计，希望是你需要的答案，o(∩_∩)o

Answer 3

使用CASE ... WHEN ....

追问

CASE是sql server里的把，oracle也能用？