sql 知识点统计 多表sql查询连接

/** * 统计知识点审核率 */@SuppressWarnings({ "unchecked", "rawtypes"})public List<KnowLedgeStatistics> doStatisticKnowledge(){//原始sql：SELECT tab2.topname ,tab2.tcount,tab1.kcount FROM ((SELECT tt.topParentName AS topname, COUNT(*) AS tcount FROM lc_knowledge_type  AS tt WHERE child=0 GROUP BY tt.topParentName) AS tab2 LEFT JOIN ((SELECT t.topParentName AS topname, COUNT(*) AS kcount FROM lc_knowledge_manager AS m LEFT JOIN lc_knowledge_type AS t ON m.knowledgeType_id = t.id WHERE STATUS>1  GROUP BY t.topParentName) AS tab1 ) ON tab2.topname=tab1.topname) ORDER BY tab1.kcount DESC,tab2.tcount ASC    final String sql = "SELECT tab2.topname ,tab2.tcount,tab1.kcount "    +"FROM ("+"(SELECT tt.topParentName AS topname, COUNT(*) AS tcount FROM lc_knowledge_type  AS tt WHERE child=0 GROUP BY tt.topParentName) AS tab2"+" LEFT JOIN (SELECT t.topParentName AS topname, COUNT(DISTINCT m.knowledgeType_id) AS kcount FROM lc_knowledge_manager AS m LEFT JOIN lc_knowledge_type AS t ON m.knowledgeType_id = t.id WHERE STATUS>2 AND t.child=0  GROUP BY t.topParentName) AS tab1  "    +" ON tab2.topname=tab1.topname) ";List<Object[]> lizt =  getHibernateTemplate().execute(new HibernateCallback()        {@Overridepublic List<Object[]> doInHibernate(Session session)throws HibernateException, SQLException {SQLQuery q = session.createSQLQuery(sql);q.setCacheable(false);return q.addScalar("topname",StandardBasicTypes.STRING).addScalar("tcount",StandardBasicTypes.INTEGER).addScalar("kcount",StandardBasicTypes.INTEGER).list();}        });List<KnowLedgeStatistics> klist = new ArrayList<KnowLedgeStatistics>();      for(Object[] obj :lizt)      {      KnowLedgeStatistics ks = new KnowLedgeStatistics();      ks.setTopParentName(obj[0].toString());      ks.setGroupLeafNodeNum((int)obj[1]);      if(obj[2]!=null)      ks.setCheckedNum((int)obj[2]);      else      ks.setCheckedNum(0);            ks.setRate( String.format("%.2f%%",  (((double)ks.getCheckedNum()) / ((double)ks.getGroupLeafNodeNum()) * 100)));      klist.add(ks);      }         return klist;}