mysql右连接，判断大小

SELECT  (CASE when c.billcycle>c.firstzm THEN c.billcycle ELSE c.firstzm END )AS firstzm,c.thirdzm,c.secondzm FROM(SELECT *FROM (SELECT CAST(MONTH(billcycle) AS CHAR)AS billcycle,CAST(COUNT(MONTH(billcycle)) AS CHAR) AS thirdzm FROM tb_ac_billpaid_yym WHERE (SELECT MONTH(billcycle))>(select MONTH(NOW())-4) AND (select MONTH(billcycle))<=(select MONTH(NOW())) GROUP  BY CAST(MONTH(billcycle) AS CHAR)) AS a RIGHT JOIN (SELECT CAST(MONTH(billcycle) AS CHAR)AS firstzm,CAST(COUNT(MONTH(billcycle)) AS CHAR) AS secondzm  FROM tb_ac_billunpaid WHERE (SELECT MONTH(billcycle))>(select MONTH(NOW())-4) AND (select MONTH(billcycle))<=(select MONTH(NOW())) GROUP  BY CAST(MONTH(billcycle) AS CHAR)) AS b ON a.billcycle=b.firstzm) AS c

对于我这样一个菜鸟我都佩服我能写出这样的sql语句。

下面我来解释一下我的这段sql语句的意思：

      首先分别从两个表中查出自己需要的数据：

（1）

SELECT CAST(MONTH(billcycle) AS CHAR)AS billcycle,CAST(COUNT(MONTH(billcycle)) AS CHAR) AS thirdzm FROM tb_ac_billpaid_yym WHERE (SELECT MONTH(billcycle))>(select MONTH(NOW())-4) AND (select MONTH(billcycle))<=(select MONTH(NOW())) GROUP  BY CAST(MONTH(billcycle) AS CHAR)

（2）

SELECT CAST(MONTH(billcycle) AS CHAR)AS firstzm,CAST(COUNT(MONTH(billcycle)) AS CHAR) AS secondzm  FROM tb_ac_billunpaid WHERE (SELECT MONTH(billcycle))>(select MONTH(NOW())-4) AND (select MONTH(billcycle))<=(select MONTH(NOW())) GROUP  BY CAST(MONTH(billcycle) AS CHAR)

这两段sql有异曲同工之处，就是在查询的时候我修改了字段的类型为char型，因为我的实体类中是String型，所以我必须修改一下字段类型；查询的条件是当前月以及前四个月。

    然后，根据月份将两个表右连接起来。

    最后一步，我是根据两个表中，月份大的为字段，查出了所有数据。

在写的时候我出现了一个问题，就是case语句还不够熟悉，case的语法是：case  when then else end；千万记住end不能少。不然就会像我一样报错咯！！！