最近处理任务碰到个sql的问题,要求把数据集分组后按某个字段统计的结果作为一个查询条件,由于自己sql基础比较差,查阅资料,花了很长时间才找出了解决方案,用groupby+having的方式可以实现,贴上我的sql如下:
SELECT t2.res_id, t2.res_name,count(record_id) a
FROM t_res_print_record t2
LEFT JOIN t_res_printset_limit t1 ON ( t1.res_id = t2.res_id AND t1.islimit =1 )
WHERE
( ( t2.print_time >= ? or ? is null ) and ( t2.print_time <= ? or ? is null )
and (t2.res_id in (?) or ? is null) )
GROUP BY t2.res_name,t2.res_id
having ((a>=? or ? is null)and (a<=? or ? is null))
SELECT t2.res_id, t2.res_name,count(record_id) a
FROM t_res_print_record t2
LEFT JOIN t_res_printset_limit t1 ON ( t1.res_id = t2.res_id AND t1.islimit =1 )
WHERE
( ( t2.print_time >= ? or ? is null ) and ( t2.print_time <= ? or ? is null )
and (t2.res_id in (?) or ? is null) )
GROUP BY t2.res_name,t2.res_id
having ((a>=? or ? is null)and (a<=? or ? is null))