如何验证group by的查询SQL中是否包含重复数据

引言：
我们在项目开发过程中，总是会碰到一些书写复杂sql时（特别是要设计到金额计算），总是会怕统计到多余或重复的记录，那又要怎么查看呢？

例子：如下SQL

SELECT b.bldg_id,b.bldg_name,SUM(pu.psu_salearea) AS pre_sale_area,IFNULL(SUM(pu.psu_sumprice),'0.00') AS pre_sale_total,IFNULL(SUM(pu.psu_sumprice)/SUM(pu.psu_salearea),'0.00') AS pre_average_price,SUM(IF(u.un_sale_status=8,pu.psu_salearea,0)) AS subscribe_area,SUM(IF(o.orderstatus=2 AND apprevestatus IN(2,4),o.deposit,0)) AS subscribe_price,IFNULL(SUM(IF(o.orderstatus=2 AND apprevestatus IN(2,4),o.deposit,0))/SUM(IF(u.un_sale_status=8,pu.psu_salearea,0)),'0.00') AS subscribe_average_price,SUM(IF(u.un_sale_status=6,pu.psu_salearea,0)) AS sign_area,SUM(IF(o.orderstatus=4 AND o.apprevestatus IN(2,4),o.contractamount,0)) AS sign_price,IFNULL(SUM(IF(o.orderstatus=4 AND o.apprevestatus IN(2,4),o.contractamount,0))/SUM(IF(u.un_sale_status=6,pu.psu_salearea,0)),'0.000000') AS sign_average_price,SUM(IF(u.un_sale_status=3,pu.psu_salearea,0)) AS no_sale_area,IFNULL(SUM(IF(u.un_sale_status=3,pu.psu_sumprice,0)),'0.00') AS no_sale_total,IFNULL(SUM(IF(u.un_sale_status=3,pu.psu_sumprice,0))/SUM(IF(u.un_sale_status=3,pu.psu_salearea,0)),'0.000000') AS no_sale_average_price,SUM(IF(o.orderstatus=2 AND apprevestatus IN(2,4),o.deposit,0))+SUM(IF(o.orderstatus=4 AND o.apprevestatus IN(2,4),o.contractamount,0)) AS back_price FROM bm_units u INNER JOIN ps_unitinfo pu ON pu.un_id=u.un_id LEFT JOIN bm_building b ON b.bldg_id=pu.bldg_id LEFT JOIN ps_order o ON o.unitid=u.un_id  WHERE pu.co_id = '13' AND u.status = 1 AND pu.status = 1 GROUP BY pu.bldg_id LIMIT 0,10  

1、从上面得出通过group by统计分组数据是可能不够正确的，因为没有办法保证统计出来的某个表的记录不会重复
2、此时需要用一个MYSQL可能大家很少用到的一个函数GROUP_CONCAT(）
3、用法如下：

SELECT GROUP_CONCAT(o.id) AS chongfu,......

4、如何辨别该函数查询出来的结果，请看下图

5、如上图，结果显示有重复的id存在，则说明统计结果可能不正确，需要继续优化SQL
6、注意，一般采用GROUP_CONCAT(）的使用情景是SQL中用了group by，但是因为联表的原因可能会产生重复的字段数据，此时需要这个函数去鉴定数据的准确性。