group by +left join+count(1)查询实例

今天要做一个新的功能开发，把项目下载下载看了看，在dao层看见下面一段数据库查询语句：

public Map<String, Object> queryConsultTotalList(ConsultParamVo vo,PageRequest pageRequest) {String proCountSql = "select doctorid, isproblem, count(1) rn from jkmh_webcms.tcm_consult where 1 = 1";String filterSql = "";proCountSql += " group by doctorid, isproblem";String totalSql = "select t.id,t.org_name, t.dept_name, t.emp_no, t.name, t.sex, nvl(sum(t.consult), 0) consult, nvl(sum(t.reply), 0) reply"+ " from (select d.id, d.org_name, d.dept_name, d.emp_no, d.name, (case when d.sex = '1' then '男' else '女' end) sex, c1.rn consult, c2.rn reply from jkmh_webcms.TB_DOCTOR_USERS d"+ " left join ("+ proCountSql+ ") c1 on d.id = c1.doctorid and c1.isproblem = '0'"+ " left join ("+ proCountSql+ ") c2 on d.id = c2.doctorid and c2.isproblem = '2') t where 1 = 1";totalSql += filterSql;totalSql += " group by t.id, t.org_name, t.dept_name, t.emp_no, t.name, t.sex";String countSql = "select count(1) from (" + totalSql + ")";                int num = (pageRequest.getPageNo() - 1) * pageRequest.getPageSize();List list = dao.createSQLQuery(totalSql, null, null, null).setFirstResult(num).setMaxResults(pageRequest.getPageSize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();int count = Integer.valueOf(dao.createSQLQuery(countSql, null, null, null).list().get(0).toString());}

 从上面可以看出主要用到两个数据库查询语句：
1、totalSql
2、countSql
可是大学数据库没学好的我看到这种略长的数据库语句就有点懵了，只能自己好好的研究一下到底神马意思
首先我先将totalSql语句还原成可以在数据库里面执行的语句如下：

select t.id,t.org_name, t.dept_name, t.emp_no, t.name, t.sex, nvl(sum(t.consult), 0) consult, nvl(sum(t.reply), 0) reply from        (          select d.id, d.org_name, d.dept_name, d.emp_no, d.name, (case when d.sex = '1' then '男' else '女' end) sex,          c1.rn consult, c2.rn reply  from  jkmh_webcms.TB_DOCTOR_USERS d          left join ( select doctorid , isproblem,count(1) rn from jkmh_webcms.tcm_consult group by doctorid, isproblem) c1          on d.id = c1.doctorid and c1.isproblem ='0'          left join ( select doctorid, isproblem, count(1) rn from jkmh_webcms.tcm_consult group by doctorid, isproblem) c2           on d.id = c2.doctorid and c2.isproblem = '2'        )t  group by t.id, t.org_name, t.dept_name, t.emp_no, t.name, t.sex

分析如下：
  1、首先我们可以先省略中间的括弧（）部分，就变成了下面这样：

 select t.id,t.org_name, t.dept_name, t.emp_no, t.name, t.sex, nvl(sum(t.consult), 0) consult, nvl(sum(t.reply), 0) reply from t  
 group by t.id, t.org_name, t.dept_name, t.emp_no, t.name, t.sex

这样做可以清晰的帮助我们看出查询的字段包括：来自t表的id、org_name、dept_name、emp_no、name、sex这是很清楚可以看出来的

至于另外两个字段nvl(sum(t.consult), 0) consult和 nvl(sum(t.reply),0) reply 我想肯定会有和我一样看不懂的人，通过查资料知道具体意思了

下面呢我就要详细的介绍一下 nvl(sum(t.consult),0)consult 到底是什么意思，我们从外到内的说，
1、首先要说的是nvl()是一个函数
   （1）函数格式：NVL( string1, replace_with)
   （2）函数功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回原来的值
       由此可以知道知道nvl函数有两个参数，string1=sum(t.comsult)   第二个参数为replace_with=0；
       那么nvl(sum(t.consult),0)  consult 的意思就是当sum(t.consult)为空的时候，返回0作为字段consult的值
       如果sum(t.consult)不为空就返回sum(t.consult)的值作为字段consult的值

2、然后要介绍sum()函数:
(1)、函数格式：SUM(列名) 
(2)、函数作用：返回数值列的总数

3、consult  为sumt(t.consult)的别名 

4、group by t.id, t.org_name, t.dept_name, t.emp_no, t.name, t.sex这句主要是涉及到group by 的用法，可以参见我上一篇转载的group by的用法
 知道这句的意思是：先按id分组，然后在按id分组的基础上再在每个组里面按org_name分组，依次类推。

<这里呢我就想记录一下我觉得需要注意的地方：
   *******：group by子句要配合聚合函数使用，并且，在配合聚合函数使用的时候，在group by子句中不要加上聚合函数处的列名（加入as了的话）
详细参见连接：点击打开链接
接下来再看看括弧里面的查询语句了： select d.id, d.org_name, d.dept_name, d.emp_no, d.name, (case when d.sex = '1' then '男' else '女' end) sex,
          c1.rn consult, c2.rn reply  from  jkmh_webcms.TB_DOCTOR_USERS d
          left join ( select doctorid , isproblem,count(1) rn from jkmh_webcms.tcm_consult group by doctorid, isproblem) c1
          on d.id = c1.doctorid and c1.isproblem ='0'
          left join ( select doctorid, isproblem, count(1) rn from jkmh_webcms.tcm_consult group by doctorid, isproblem) c2 
          on d.id = c2.doctorid and c2.isproblem = '2'
1、select 后面查询的字段主要来自三张表分别是 :
d表 （id、org_name、dept_name、emp_no、name、sex);
c1表（rn 字段重命名为 consult）
c2表（rn 字段重命名为 reply）
主要是d表左联c1表在左联c2表形成了t表

count(1) rn 表示将表jkmh_webcms.tcm_consult中的值按照doctorid, isproblem进行分组，分组后各组的统计值count(1)，并将该统计值重命名为rn