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()
        {

			@Override
			public 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;
	}