-- 以下两个查询效率相当，查询时间均为0.0xx秒，~ 但是Hibernate HQL不支持from后面接子查询，可以直接使用SQL方式解决，ibatis当然可以用了。
SELECT count(*) from (
SELECT sum(t2.STATE) as vnSum, t1.ID FROM
vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t where t.vnSum = 0;
SELECT count(*) FROM vnetinfo_table v ,(
SELECT sum(t2.STATE) as vnSum, t1.ID as idd FROM
vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t
where v.ID = t.idd and t.vnSum = 0;
-- 查询效率太低了，查询时间均为0.5xx秒左右。要比以上两个SQL耗时很多倍。~ Hibernate支持在where后用子查询作为查询条件。
SELECT count(*) FROM vnetinfo_table v WHERE v.id in
(SELECT CASE WHEN SUM(t2.STATE)=0 THEN t1.ID ELSE '' END
FROM vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID);
-- 使用join方式替换in 查询时间均为0.09x秒左右。明显提高了效率，但是Hibernate HQL任然不支持在这种联合子查询方式。
SELECT count(*) FROM vnetinfo_table v INNER JOIN (SELECT CASE WHEN SUM(t2.STATE)=0 THEN t1.ID ELSE '' END as idd
FROM vnetinfo_table t1, vm_vn_table t2
WHERE t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t ON v.ID = t.idd and t.idd IS not NULL;
附：Mysql中关于Exists用法的介绍请参见：http://www.nowamagic.net/librarys/veda/detail/639
posted on 2013-08-21 13:47
David1228 阅读(3751)
评论(2) 编辑 收藏