sql多表关联后聚合函数统计数值翻倍和脏数据bug与预防

在关联N个表的时候查询使用聚合函数的时候需要指定group by对象.然而group by常常破坏连接性

连接有内连接和外连接.还有左连接和右连接.导致出现脏数据和统计值失真

我在工作中很少使用左连接和右连接.这样就很容易导致统计出来的数据失真.比如sheng表表示省 shi表表示市.区表表示区里面的人数

这里不声明外键和主键的创表语句.

create sheng(

pk_sheng_id int,

name varchar(20)--省名

)

create shi(

pk_shi_id int,--市的主键

fk_sheng_id,--省的外键

name varchar(20)--市名

)

create qu(--区或者县

pk_qu_id int,--区的主键

fk_sheng_id int,--省的外键

fk_shi_id int,--市的外键

name varchar(20),--区名

people_population int--现在区里面的人数

)

如果是指定省统计人数

select sheng.name as name,sum(qu.people_population) from sheng,qu

where

sheng.name=(select name from sheng where name='湖北')

qu.fk_sheng_id=sheng.pk_sheng_id

group by sheng.name

如果不指定自连接.结果很可能翻倍.我们国家应该是34个行政区域.那么就会翻34倍.

如果指定市所在省份的人数.最好三个表的关系都连接一下.不然就会出现错误数据.

解决方法就是用自连接解决统计值翻倍问题.用三个关联相互.解决脏数据问题

原理应该是因为外连接导致.

统计翻倍的原因在于.
表a的值假设符合条件的有2条,而对应到表b里面有多条.如果group by a表里面的内容那么一定会翻倍翻两倍.如果表a符合查询条件的有n条.而表b里面有m条(m>=n)那么统计的显示值会翻n倍.
脏数据的问题在于查询条件限制不准确.
本来认为应该限制的.结果没有限制.也就其他人认为不符合的查询结果而机器实际上搜索出来符合结果就会带来脏数据.显示过的的值.脏数据本来没有值.结果由于group by之后会复制上面一条符合条件的值显示上去.原理讲完了.

为了防止出现n!=1的情况,我们搜索的时候可以强制绑定搜索结果比如  select id from a where a.name='' limit 1

这样 a表的值就只有一条了