数据库知识理解

最近做统计，在sql和程序上遇到一些问题，特此记录一下。（数据库类型MYSQL）

1.count()函数

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入），所以不想让某些数据查到可以先将它们设为null

2.case when ..then..else..end

网上看了很多例子，让我以为这就是一个逻辑判断的语句，就相当于是程序中的if...else,但事实上不只是这样。数据库中的很多函数在使用之后会默认生成一个虚拟列，这个也不例外。当使用它之后，便生成了一个虚拟的列，如果把它和count函数结合使用

count(CASE WHEN VT_POLICY.EFFECTDATE<'2014-01-01' and VT_POLICY.EFFECTDATE>'2012-01-01' THEN 1 else null END)

你会发现，如果else之后不是‘null’，而是换成‘0’，则最后统计出的不是你想要的结果。这里附上一个完整的sql

SELECT
    count(CASE WHEN VT_POLICY.EFFECTDATE<'2014-01-01' and VT_POLICY.EFFECTDATE>'2012-01-01' THEN 1 else null END) count, #0都没用
    VT_DEPARTMENT.DEPTABBR
FROM
    VT_POLICY
RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM = VT_DEPARTMENT.DEPTID 
GROUP BY
    VT_DEPARTMENT.DEPTABBR

3.继续说count，按某一条件去统计总数，比如按部门统计结果，看看每个部门有多少结果。并且如果某个部门没有数据，要显示0,而不是null，要怎么做？

我们正常的思路一般是把查询条件放到where中去，加上group by，然后使用count去统计，比如下面：

SELECT
    count(*) count,
    VT_DEPARTMENT.DEPTABBR
FROM
    VT_POLICY
RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM = VT_DEPARTMENT.DEPTID 
where VT_POLICY.EFFECTDATE<'2014-01-01' and VT_POLICY.EFFECTDATE>'2012-01-01'
GROUP BY
    VT_DEPARTMENT.DEPTABBR

结果如下：

事实上还有其他部门，由于没有数据，所以就没有显示，那如果让没有数据的显示0要怎么改呢？----把条件放到count中，示例如下，上面的sql稍作修改：

SELECT
    count(CASE WHEN VT_POLICY.EFFECTDATE<'2014-01-01' and VT_POLICY.EFFECTDATE>'2012-01-01' THEN 1 else null END) count, #0都没用
    VT_DEPARTMENT.DEPTABBR
FROM
    VT_POLICY
RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM = VT_DEPARTMENT.DEPTID 
GROUP BY
    VT_DEPARTMENT.DEPTABBR

结果如下：

我觉得之所以现在之所以能统计出所有的数据是因为现在的查找范围更大了，现在的范围是“VT_POLICY RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM = VT_DEPARTMENT.DEPTID ”，而之前查找的范围却在此基础上加了“where”限制，所以最终找的的数据就没有这些部门，自然count也就统计不到了。

4.CONCAT函数，字符串连接函数

这次还用到了字符串连接的函数，CONCAT。用法很简单，CONCAT（str1,str2,...）

例子：

SELECT count(CASE WHEN VT_RISK.SUBRISK='M' AND VT_POLICY.MODIFYTIME>CONCAT(VT_RISK.WAITBEGIN,' 00:00:00') 
and VT_POLICY.MODIFYTIME<CONCAT(VT_RISK.WAITEND,' 23:59:59') THEN 1 else null END) count,VT_DEPARTMENT.DEPTABBR abbr
FROM VT_POLICY LEFT JOIN VT_RISK ON VT_POLICY.CONTNO=VT_RISK.CONTNO RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM=VT_DEPARTMENT.DEPTID
GROUP BY VT_DEPARTMENT.DEPTABBR

5.hibernate在查询count时的问题

项目中使用的是hibernate，但这次查询的sql较为复杂，所以直接使用原生sql更方便些。hibernate本身也支持原生sql，但奇怪的问题来了，每次执行类似上面的sql时总是回报错，错误内容是‘DEPTABBR’列找不到。真是一头雾水，上面的sql全部都能直接在mysql中执行，但在程序里却报错了，经过试探，还好最后找到了方法。

修改之后的代码：红色为修改部分

session = this.getHibernateTemplate().getSessionFactory().openSession();
            String condition="CASE WHEN VT_POLICY.VISITSTATE=?";
            if(!"".equals(StringUtil.trim(beginDate))){
                condition=condition+ " and VT_POLICY.MODIFYTIME>=?";
            }
            if(!"".equals(StringUtil.trim(endDate))){
                condition=condition+ " and VT_POLICY.MODIFYTIME<=?";
            }
            condition=condition+" THEN 1 else null END";
            
            String sql="SELECT count("+condition+") count, VT_DEPARTMENT.DEPTABBR abbr"
                    + " FROM"
                    + " VT_POLICY RIGHT JOIN VT_DEPARTMENT ON VT_POLICY.MANAGECOM = VT_DEPARTMENT.DEPTID"
                    + " GROUP BY VT_DEPARTMENT.DEPTABBR";
            SQLQuery query = session.createSQLQuery(sql);
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.addScalar("count",Hibernate.INTEGER);
            query.addScalar("abbr", Hibernate.STRING);
            int i=0;
            query.setString(i++, StringUtil.trim(visitstate));
            if(!"".equals(StringUtil.trim(beginDate))){
                query.setString(i++, StringUtil.trim(beginDate+" 00:00:00"));
            }
            if(!"".equals(StringUtil.trim(endDate))){
                query.setString(i++, StringUtil.trim(endDate+" 23:59:59"));
            }
            return query.list();

如上，就加了2行，query.addScalar()。英语不太好，按照官方API解释大概是要设置返回什么内容。
加上之后程序就正常了，可以找到DEPTABBR列了，O(∩_∩)O哈哈~

据我猜测，我觉得可能在hibernate中使用count函数默认只能返回一列，因为我在把DEPTABBR去掉就正常了，但是把DEPTABBR换成其他列也还是报错，所以要手动告诉hibernate我要什么。

6.就在程序功能要完结的时候又遇到了问题，我在jsp中使用了struts的iterate标签，然后在为其传值的时候是将值放入了request范围域中，结果这个标签竟然无视我的传值，百度之后发现，struts标签的值要放到ActionContext中~

7.这次统计明显感觉到使用左联，右联的速度（a left join b）要高于直接使用from a,b,同样的数据，使用前者1秒都不到，而后这却要10多秒，差距很明显。