-
-
flybird3000 数据达人Lv4
发表于2020-12-26 14:04
悬赏500
未解决
用ABI做了一个报表，可以统计出内容，但是只要在后面增加一列拆除点位数的算法后，前面的计算就为0了，请问下是什么原因，我把两个表都导出来了，见附件。
附件：
分析表_20201226_135901_有问题.zip
(102.67 KB, 下载次数: )
分析表_20201226_120322.zip
(102.78 KB, 下载次数: )
最近看过此主题的会员
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
图片上传的有问下，重新发下截图看下
统计表.zip
(13.68 KB, 下载次数: )
-
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
