数据验证之查询统计数据

我们做业务系统测试的时候，经常需要对页面展示的数据或后台传输的数据进行数据验证，即验证数据的正确性，本篇总结下测试过程中的常用的数据库查询统计的方法

数据库：mysql

1、简单的分组查询 group by

如统计A表中不同车型vehicle_class的数量和交易金额

 SELECT COUNT(1) AS 总量,  SUM(CAST(IFNULL(fee, 0) * 0.01 AS DECIMAL (10, 2))) AS 总额, vehicle_class  FROM A 
 GROUP BY vehicle_class;

解释：

1）这里的金额fee字段是int类型，即数据库存的是单位分，页面显示需要转换单位元，且保留小数点后2位，decimal(10,2)可以存储8位整数2位小数的数字

2）CAST是数据类型转换的函数，用法为：CAST(字段名 as 转换的类型 )

3） IFNULL（fee,0），如果fee为NULL则为0，如果不为NULL则为具体的fee值

结果如下：

   总量    总额     vehicle_class

   550    52808.00    3

   450    74000.00    4

2、包含判断的统计 case when

如统计A表中不同车种的总量和总额，以及所有合计总数，总金额

车种的需求为：当vehicle_class=1、2时，对应的车种为：小车；当vehicle_class=3、4、5、6时，对应的车种为：客车；当vehicle_class=7、8、9时对应的车种为：货车

SELECT 
COUNT(1) AS 总量,
SUM(fee) AS 总额,
SUM((CASE WHEN a.vehicle_class IN (1,2) THEN 1 ELSE 0 END)) AS '小车数量',
SUM((CASE WHEN a.vehicle_class IN (1,2) THEN fee ELSE 0 END)) AS '小车金额',
SUM((CASE WHEN a.vehicle_class IN (3,4,5,6) THEN 1 ELSE 0 END)) AS '客车数量',
SUM((CASE WHEN a.vehicle_class IN (3,4,5,6) THEN fee ELSE 0 END)) AS '客车金额', 
SUM((CASE WHEN a.vehicle_class IN (7,8,9) THEN 1 ELSE 0 END)) AS '货车数量',
SUM((CASE WHEN a.vehicle_class IN (7,8,9) THEN fee ELSE 0 END)) AS '货车金额'
 FROM A;

结果如下：

总量   总额  小车数量   小车金额    客车数量   客车金额    货车数量    货车金额
3000  3000  1000      1000      1000      1000       1000      1000

再比如用户表(t_user)，统计不同用户类型(type)男女数量分别是多少

SELECT  
CASE 
WHEN TYPE=1 THEN '超级管理员'
WHEN TYPE=2 THEN '普通管理员'
ELSE '其他'
END '用户类型',
COUNT(CASE WHEN sex='F' THEN '女性' END ) AS '女性数量',
COUNT(CASE WHEN sex='M' THEN '男性' END ) AS '男性数量'
FROM t_user
GROUP BY TYPE

结果如下：

用户类型    女性数量    男性数量
超级管理员   1         0
普通管理员   150       300

3、多表查询结果累计统计 union all

如统计对应条件，不同状态的总量和总金额

SELECT SUM(t.cnt), SUM(t.fee), t.st FROM (
 SELECT COUNT(1) AS cnt, SUM(amount) AS fee, order_status AS st
   FROM A
  WHERE ?
 GROUP BY order_status
 UNION ALL 
 SELECT COUNT(1) AS cnt, SUM(amount) AS fee, order_status AS st 
  FROM B
   WHERE ? 
 GROUP BY order_status
) t
    GROUP BY st;

结果如下：

SUM(t.cnt)    SUM(t.fee)    st
100           23000         2
200           54900         3