-
-
flybird3000 数据达人Lv4
发表于2020-12-26 14:04
悬赏500
未解决
用ABI做了一个报表,可以统计出内容,但是只要在后面增加一列拆除点位数的算法后,前面的计算就为0了,请问下是什么原因,我把两个表都导出来了,见附件。



附件:


14个回答
-
3516853030 小试身手Lv3
本帖最后由 3516853030 于 2020-12-27 17:23 编辑
图片上传的好像有问题,重新发下截图看下
-
flybird3000 数据达人Lv4
3516853030 发表于 2020-12-26 14:06
图片上传的有问下,重新发下截图看下
你要看哪个部分的设置,在我给你们的报表里面都有的。如果要看导出后的效果,我倒两张报表出来,你们看看结果。
-
flybird3000 数据达人Lv4
3516853030 发表于 2020-12-26 14:06
图片上传的有问下,重新发下截图看下

-
flybird3000 数据达人Lv4
3516853030 发表于 2020-12-26 19:02
导致数据显示不出来的原因是添加了sum吗
不是,是在后面那个拆除点位数那个地方增加了一个过滤条件,然后就完全不对了。详情见下图:

-
flybird3000 数据达人Lv4
3516853030 发表于 2020-12-26 19:02
导致数据显示不出来的原因是添加了sum吗
感觉很奇怪,后面的有一列会影响到前面的显示,而且发现,即使没有这个问题,整个统计的数据还有有些出入,不知道是哪个地方有问题或者系统有BUG。如果需要你们可以远程登录上来看看。
-
3516853030 小试身手Lv3
把受影响的两列单独拿出来计算下看下,然后单独给拆除点这一列拿出来再计算下看下,把两个的计算结果里的计算详情里的sql拿出来看下,看是哪里不对劲
-
flybird3000 数据达人Lv4
我把其他列都删除完了,只留了客户规模和拆迁点位数,然后测试了一下,只要我把拆除点位数里面的过滤条件(F_QLXQ_NEW.XQZT='拆除'),就会出现第一个为0的情况,如果把过滤条件去掉,就马上正常了。麻烦看下有什么问题呢?为什么第二列的过滤条件会影响第一列呢?
异常的SQL语句:
select * from ( select a.C5 as C5,b.D5 as D5,a.B5 as B5,a.A4 as A4
from (
select sum(a.KHGM) as C5,a.YX as B5, LEFT(a.YX,2) as A4
from f_qlxq_new a
where ((a.YX = '010101' or a.YX = '010102' or a.YX = '010103' or a.YX = '010104' or a.YX = '010105' or a.YX = '010106' or a.YX = '010107' or a.YX = '010108' or a.YX = '010109' or a.YX = '010110' or a.YX = '010111' or a.YX = '010112' or a.YX = '010113' or a.YX = '020101' or a.YX = '020102' or a.YX = '020103' or a.YX = '020104' or a.YX = '020105' or a.YX = '020106' or a.YX = '020107' or a.YX = '020108' or a.YX = '020109' or a.YX = '030101' or a.YX = '030102' or a.YX = '030103' or a.YX = '030104' or a.YX = '030105' or a.YX = '030106' or a.YX = '030107') AND (a.YX like '01%' or a.YX like '02%' or a.YX like '03%') AND (a.ZKSX = '在库'))
group by LEFT(a.YX,2),a.YX) a
inner join (
select count(a.XQZT) as D5,a.YX as B5, LEFT(a.YX,2) as A4
from f_qlxq_new a
where ((a.YX = '010101' or a.YX = '010102' or a.YX = '010103' or a.YX = '010104' or a.YX = '010105' or a.YX = '010106' or a.YX = '010107' or a.YX = '010108' or a.YX = '010109' or a.YX = '010110' or a.YX = '010111' or a.YX = '010112' or a.YX = '010113' or a.YX = '020101' or a.YX = '020102' or a.YX = '020103' or a.YX = '020104' or a.YX = '020105' or a.YX = '020106' or a.YX = '020107' or a.YX = '020108' or a.YX = '020109' or a.YX = '030101' or a.YX = '030102' or a.YX = '030103' or a.YX = '030104' or a.YX = '030105' or a.YX = '030106' or a.YX = '030107') AND (a.YX like '01%' or a.YX like '02%' or a.YX like '03%') AND (a.XQZT = '拆除') AND (a.ZKSX = '在库'))
group by LEFT(a.YX,2),a.YX) b
on ((a.B5=b.B5) AND (a.A4=b.A4))
order by a.A4,a.B5 ) as temp_ limit 500
正常的SQL语句:
select sum(a.KHGM) as C5,count(a.XQZT) as D5,a.YX as B5, LEFT(a.YX,2) as A4
from f_qlxq_new a
where ((a.YX = '010101' or a.YX = '010102' or a.YX = '010103' or a.YX = '010104' or a.YX = '010105' or a.YX = '010106' or a.YX = '010107' or a.YX = '010108' or a.YX = '010109' or a.YX = '010110' or a.YX = '010111' or a.YX = '010112' or a.YX = '010113' or a.YX = '020101' or a.YX = '020102' or a.YX = '020103' or a.YX = '020104' or a.YX = '020105' or a.YX = '020106' or a.YX = '020107' or a.YX = '020108' or a.YX = '020109' or a.YX = '030101' or a.YX = '030102' or a.YX = '030103' or a.YX = '030104' or a.YX = '030105' or a.YX = '030106' or a.YX = '030107') AND (a.YX like '01%' or a.YX like '02%' or a.YX like '03%') AND (a.ZKSX = '在库'))
group by LEFT(a.YX,2),a.YX
order by LEFT(a.YX,2),a.YX