birt中根据两个表的差别生成一个饼图

最近遇到一个问题，有两个表，这两个表的结构是完全一样的，假设为A表和B表。其中关键的字段有test_case_name, min_value和changelist。我想得到所有A表中min_value比B表的min_value大的个数，小的个数和相等的个数，当然前提条件是test_case_name是相同的，并且changelist是制定的某个值。然后将这个个数画出一个饼图来，饼图中应该有三个部分，分别是Better，Worse和Same，分别代表刚刚计算得到的相比较小的个数，大的个数和相等的个数。

一开始，自己是没有很好的思路，我可以得到表A和表B中test_case_name相同，changelist为指定值的两个表的数据，通过以下命令可以得到结果。

select v1.test_case_name,
       v1.min_value as v1min,
       v2.min_value as v2min,
       from table_A as v1
       JOIN table_B as v2
       ON v1.test_case_name = v2.test_case_name
       AND v1.changelist = "2000"
       and v2.changelist = "2000";

我也可以分别得到A的min_value比B的min_value小于的个数，大于的个数和等于的个数。如下的命令

select count(*)
       from table_A as v1
       JOIN table_B as v2
       ON v1.test_case_name = v2.test_case_name
       AND v1.changelist = "2000"
       and v2.changelist = "2000"
where v1.min_value < v2.min_value;

结果如下所示：

+----------+
| count(*) |
+----------+
|      172 |
+----------+

我可以用三条语句分别得到小于的个数，大于的个数和等于的个数。可问题是，由于我的查询语句是需要放到birt中的，它里面不能同时放三个查询语句，这就导致我无法用上面的语句完成我的目的。要是只通过一条语句既能够得到小于的个数，大于的个数，还有等于的个数，那该有多好呀。

到此被这个问题难住了，纠结了好几天都没有进展。然后就问网友是不是有可能有一种方法可以在一条查询语句中得到这三个值呢，有一个网友跟我说可以尝试一下case when语句。于是就上网搜了一下，到底什么是case when呢？case when有三种使用的语法。

其中一种用法为：

select name,  
case   
        when birthday<'1981' then 'old'  
        when birthday>'1988' then 'young'  
        else 'ok' 
end  
from table1;

于是我就尝试用case when语句去实现在一条sql语句中把大于的值，小于的值和等于的值都计算出来。

select v1.test_case_name,
       v1.min_value as v1min,
       v2.min_value as v2min,
       (case
         when v1.min_value > v2.min_value then
          'better'
         when v1.min_value = v2.min_value then
          'same'
         when v1.min_value < v2.min_value then
          'worse'
       end) as BZ
from table_A as v1
join table_B as v2
on v1.test_case_name = v2.test_case_name
and v1.changelist = "2015"
and v2.changelist = "2015"

上面语句会将表A中和表B中test_case_name相同，并且changelist等于对应值的test_case_name, v1min, v2min和BZ值得到，这里的BZ其实就是case when的输出值。

+-------------------------+----------+----------+--------+
| test_case_name          | v1min    | v2min    | BZ     |
+-------------------------+----------+----------+--------+
| case_1                  |   477.00 |   595.00 | worse  |
| case_2                  |   178.00 |   488.00 | worse  |
| case_3                  |    87.00 |    91.00 | worse  |
| case_4                  |   408.00 |   537.00 | worse  |
| case_5                  |  4257.00 |  2801.00 | better |
| case_6                  |   138.00 |   219.00 | worse  |

这个倒是得到了对应case的BZ值，但是这还不是我想要的结果，我想要得到BZ中better, worse和same的分别对应的个数。于是就有了下面的sql语句。

SELECT BZ,count(BZ)
from
(select v1.test_case_name,
       v1.min_value as v1min,
       v2.min_value as v2min,
       (case
         when v1.min_value > v2.min_value then
          'better'
         when v1.min_value = v2.min_value then
          'same'
         when v1.min_value < v2.min_value then
          'worse'
       end) BZ
from table_A as v1
join table_B as v2
on v1.test_case_name = v2.test_case_name
and v1.changelist = "2015"
and v2.changelist = "2015"
) A
GROUP BY BZ

得到的结果如下：

+--------+-----------+
| BZ     | count(BZ) |
+--------+-----------+
| better |        33 |
| worse  |       172 |
+--------+-----------+

上面的结果仍然有些小的缺陷，那就是并没有把same的值显示出来，尽管它的值为0，但是我并不希望它就不显示。因此就有了如下的sql语句

select BZ1, ifnull(SL,0) 
from  
	(select 'better' BZ1  from dual union 
	select 'same' BZ1 from dual union 
	select 'worse' BZ1 from dual) as B 
left join (select BZ, count(BZ) as SL 
	from (select v1.test_case_name, v1.min_value as v1min, v2.min_value as v2min, 
			(case  
				when v1.min_value > v2.min_value then 'better' 
				when v1.min_value = v2.min_value then  'same' 
				when v1.min_value < v2.min_value then 'worse' 
			end) as BZ 
		from table_A as v1 
		join table_B as v2 
		on v1.test_case_name = v2.test_case_name 
		and v1.changelist = "2015" 
		and v2.changelist = "2015"
		) as A  
	group by BZ) as C 
on(B.BZ1=C.BZ)

其中用到了dual语法，mysql也存在和oracle类似的dual虚拟表，官方申明纯粹是为了满足select ... from ...这一习惯问题。mysql会忽略对该表的引用。有了dual，你可以执行如下的sql命令。

mysql> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

上述命令看起来真是让人感到糊涂，一条查询语句竟然有这么长，在此我就详细解释一下这条sql语句。

万变不离其宗，这么长的一条sql语句肯定是逐层嵌套得来的，那么第一步我们就要得到他最外面的一层。仔细观察我们发现它的最外一层是一个left join语句。left join的语法如下。

select * from B
left join C 
on B.a = C.b

我们将我们分析的sql语句将left join的骨架提取出来即是

select BZ1, ifnull(SL, 0) from B
left join C
on B.BZ1=C.BZ

这里面的B即是下面这条语句的结果

SELECT 'better' BZ1  FROM dual UNION 
SELECT 'same' BZ1 FROM dual UNION 
SELECT 'worse' BZ1 FROM dual

该sql语句的结果为：

+--------+
| BZ1    |
+--------+
| better |
| same   |
| worse  |
+--------+

以上即为表B的内容。接着我们再看C，表C又是什么呢？其实这个并不难，我们只要找到as就行，as C前面的一部分就是C。

SELECT BZ, COUNT(BZ) AS SL 
FROM (
SELECT 
v1.test_case_name, 
v1.min_value as v1min, 
v2.min_value as v2min, 
(CASE  
WHEN v1.min_value < v2.min_value * 0.95 THEN 'better' 
WHEN v1.min_value > v2.min_value * 1.05 THEN 'worse' 
ELSE 'Same' 
end) BZ 
FROM table_A AS v1 
JOIN table_B AS v2 
ON v1.test_case_name = v2.test_case_name 
AND v1.changelist = "2015" AND v2.changelist = "2015"
) A  
GROUP BY BZ

该语句的执行结果为：

+--------+----+
| BZ     | SL |
+--------+----+
| better | 27 |
| worse  |  1 |
+--------+----+

实际上上面的内容就是表C的内容。

最后根据条件B.BZ1=C.BZ将两个表链接起来。因为C中BZ只有两个值，但是B中的BZ1却又三个值，因为是B left join C，所以是以B表为主，自然BZ1会有三个值，因为在C表中‘same’没有值，所以会显示为NULL，即如下的结果：

<pre name="code" class="plain">+--------+------+
| BZ1    | SL   |
+--------+------+
| better |   27 |
| worse  |    1 |
| same   | NULL |
+--------+------+

我们可以在SL上做一个小的操作即为ifnull(SL, 0)。它的含义为，如果SL为NULL，则显示为0，否则就显示本身的值即可。所以最终的结果如下：

+--------+------+
| BZ1    | SL   |
+--------+------+
| better |   27 |
| worse  |    1 |
| same   |    0 |
+--------+------+

这样就得到了我们想要的结果。

心得：如果觉得一些东西特别复杂，那就静下心来，慢慢分析，抽丝剥茧，将复杂的东西分解为简单的我们可以理解，最终就能搞明白复杂的了。复杂的东西都是由简单的东西组成的，没有简单，何来复杂。