sql 统计整理

weixin_30878361

于 2019-04-29 17:42:00 发布

阅读量60

点赞数

CC 4.0 BY-SA版权

文章标签： java 数据库

原文链接：http://www.cnblogs.com/scode2/p/10791867.html

本文探讨了Pgsql中字符字段转整型的查询方法，展示了使用CAST和::操作符进行数据类型转换的技巧。同时，介绍了MyBatis框架下实现IN条件查询的XML语法，以及Mysql中利用子查询进行数据汇总的实例。

Pgsql

字符字段转整型查询：

SELECT mon_id as staTime,SUM (CAST ( index_value AS INT )) AS totalCount FROM aidata.rep_index_values_m

SELECT ext_dim1 sex, sum(index_value::NUMERIC) total FROM aidata.rep_index_values_y T

mybatis in 查询：

<if test="orgIdList != null">
            <if test="orgIdList.size() > 0">
                and org_id IN
                <foreach collection="orgIdList" item="item" open="("
                         separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </if>

Mysql:

子查询

SELECT
        temp1.count AS total,
        temp2.count AS num1,
        temp3.count AS num2 
    FROM
    ( SELECT count( 1 ) AS count FROM t_e_xiexiexie WHERE STATUS = '1' ) temp1,
    ( SELECT count( 1 ) AS count FROM t_e_xiexiexie WHERE true AND createtime >= '2019-06-13' AND createtime < '2019-06-14' ) temp2,
    ( SELECT count( 1 ) AS count FROM t_e_xiexiexie WHERE STATUS = '9' AND lastmodifytime >= '2019-06-13' AND lastmodifytime < '2019-06-14' ) temp3