mysql 多表统计的笛卡尔积的问题

龙影 发布于 2014/06/10 09:33
阅读 1K+
收藏 0

SQL代码如下:

SELECT e.userno,e.fullname, COUNT(sp.id) pactct, SUM(sp.tamt) pactamt, COUNT(so.id)ordct, SUM(so.zhtamt) ordamt, COUNT(c.id)custct, COUNT(coct.id)CONCT, COUNT(po.id)porct, SUM(po.zhtamt) poram FROM hr_employee e LEFT JOIN scm_salepact sp ON sp.saler=e.id AND sp.shstate=2 AND sp.ctime >='2014-01-01' AND sp.ctime<='2014-12-31' LEFT JOIN scm_saleorder so ON so.saler=e.id AND so.shstate=2 AND so.ctime >='2014-01-01' AND so.ctime<='2014-12-31' LEFT JOIN crm_customer c ON c.creater=e.id AND c.ctime >='2014-01-01' AND c.ctime<='2014-12-31' LEFT JOIN crm_contactrecord coct ON coct.lxr=e.id AND coct.ptime >='2014-01-01' AND coct.ptime<='2014-12-31' LEFT JOIN scm_salepriceorder po ON po.creater=e.id AND po.shstate=2 AND po.ctime >='2014-01-01' AND po.ctime<='2014-12-31' 

而运行的结果却是:

这些统计值都是重复了 

求解!!

加载中
0
龙影
龙影

最后的sql如下:

SELECT e.userno,e.fullname, COUNT(distinct sp.id) pactct, SUM(distinct sp.tamt) pactamt, COUNT(distinct so.id)ordct, SUM(distinct so.zhtamt) ordamt, COUNT(distinct c.id)custct, COUNT(distinct coct.id)CONCT, COUNT(distinct po.id)porct, SUM(distinct po.zhtamt) poram FROM hr_employee e LEFT JOIN scm_salepact sp ON sp.saler=e.id AND sp.shstate=2 AND sp.ctime >='2014-01-01' AND sp.ctime<='2014-12-31' LEFT JOIN scm_saleorder so ON so.saler=e.id AND so.shstate=2 AND so.ctime >='2014-01-01' AND so.ctime<='2014-12-31' LEFT JOIN crm_customer c ON c.creater=e.id AND c.ctime >='2014-01-01' AND c.ctime<='2014-12-31' LEFT JOIN crm_contactrecord coct ON coct.lxr=e.id AND coct.ptime >='2014-01-01' AND coct.ptime<='2014-12-31' LEFT JOIN scm_salepriceorder po ON po.creater=e.id AND po.shstate=2 AND po.ctime >='2014-01-01' AND po.ctime<='2014-12-31' GROUP BY E.USERNO,E.FULLNAME 

执行结果如下:

解决方法是count和sum里使用distinct来解决,然后group by。

但是又引出了一个问题 就是我只是想显示统计字段不全为0的数据显示

请问如何解决?

1
白与黑
白与黑
select id,name,count(distinct id) from tablename    加上count(distinct id)试试
龙影
龙影
嗯 已经添加了distinct来解决了。多谢。也是突然想到了。
0
龙影
龙影


这个是完整的截图

0
杨延庆
杨延庆
你group by了么?
龙影
龙影
刚开始贴的sql是有问题 没有将groupby贴出。但是我想体现的是重复的问题 与group by 无大的关系
0
杨延庆
杨延庆
你构造一个字段,判断各行全为0时为true,不全为0时为false,然后只显示这个字段为true或false的值
返回顶部
顶部