select
sys_guid(),
count(jsb1.zgh) nanrs,
count(jsb2.zgh) nvrs,
count(jsb3.zgh) nan_nv_rs,
t.yxmc
from dm_yxb t
left join (select zgh,xb,xydm from sz_jsxxb where xb='1' and xydm is not null) jsb1 on t.yxdm=jsb1.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb='2' and xydm is not null) jsb2 on t.yxdm=jsb2.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb!='1' and xb!='2' and xydm is not null) jsb3 on t.yxdm=jsb3.xydm
group by t.yxmc上面的写法是由子查询优化过来的结果。效果还可以,从31秒一下到了1.09秒,把我高兴了一把。哐哐哐的兴奋一顿后,发现是有个问题,就是查询的结果不如人意,有不会报错的严重数据错误,如下图:查询出的结果就是如上面表所示,“NANRS” , “NVRS” 的内容是一样的。这种问题,看着不报错,事实上,这种问题最大。我只是想知道关键的列值为什么会重复?
sys_guid(),
count(jsb1.zgh) nanrs,
count(jsb2.zgh) nvrs,
count(jsb3.zgh) nan_nv_rs,
t.yxmc
from dm_yxb t
left join (select zgh,xb,xydm from sz_jsxxb where xb='1' and xydm is not null) jsb1 on t.yxdm=jsb1.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb='2' and xydm is not null) jsb2 on t.yxdm=jsb2.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb!='1' and xb!='2' and xydm is not null) jsb3 on t.yxdm=jsb3.xydm
group by t.yxmc上面的写法是由子查询优化过来的结果。效果还可以,从31秒一下到了1.09秒,把我高兴了一把。哐哐哐的兴奋一顿后,发现是有个问题,就是查询的结果不如人意,有不会报错的严重数据错误,如下图:查询出的结果就是如上面表所示,“NANRS” , “NVRS” 的内容是一样的。这种问题,看着不报错,事实上,这种问题最大。我只是想知道关键的列值为什么会重复?
select
sys_guid(),
x.rs nanrs,
y.rs nvrs,
z.rs nan_nv_rs,
t.yxmc
from dm_yxb t
left join (select count(zgh) rs,xydm from sz_jsxxb where xb='1' and xydm is not null group by xydm) x on t.yxdm=x.xydm
left join (select count(zgh) rs,xydm from sz_jsxxb where xb='2' and xydm is not null group by xydm) y on t.yxdm=y.xydm
left join (select count(zgh) rs,xydm from sz_jsxxb where xb!='1' and xb!='2' and xydm is not null group by xydm) z on t.yxdm=z.xydm
select
count(jsb1.zgh) nanrs,
count(jsb2.zgh) nvrs,
count(jsb3.zgh) nan_nv_rs,
t.yxdm
from dm_yxb t
left join (select zgh,xb,xydm from sz_jsxxb where xb='1' and xydm is not null) jsb1 on t.yxdm=jsb1.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb='2' and xydm is not null) jsb2 on t.yxdm=jsb2.xydm
left join (select zgh,xb,xydm from sz_jsxxb where xb!='1' and xb!='2' and xydm is not null) jsb3 on t.yxdm=jsb3.xydm
group by t.yxdm
上面这个就是使用yxdm进行分组的啊,不好使。除非在left join里面分组才是对的
上面这个就是使用yxdm进行分组的啊,不好使。除非在left join里面分组才是对的可能不能先左连再count,你限制一个yxdm的值,把dm_yxb和sz_jsxxb左连,假设sz_jsxxb表zgh,xb,xydm相同的数据有三条,看左连后的结果数据有几条
但是也不像你说的那样,因为left join而导致这两列的值一样,因为有的也不一样。但是前边那几行重复的数据,我确定肯定不正确,因为男女人数肯定不一样。而且这院系也没有那么多人数。你看结果图:
select
sys_guid(),
sum(jsb1.zgh) nanrs,
sum(jsb2.zgh) nvrs,
sum(jsb3.zgh) nan_nv_rs,
t.yxmc
from dm_yxb t
left join (select count(zgh) zgh,xydm from sz_jsxxb where xb='1' and xydm is not null group by yxdm) jsb1 on t.yxdm=jsb1.xydm
left join (select count(zgh) zgh,xydm from sz_jsxxb where xb='2' and xydm is not null group by yxdm) jsb2 on t.yxdm=jsb2.xydm
left join (select count(zgh) zgh,xydm from sz_jsxxb where xb!='1' and xb!='2' and xydm is not null group by yxdm) jsb3 on t.yxdm=jsb3.xydm
group by t.yxmc
select sys_guid(),
count(case when xb ='1' then jsb1.zgh end) nanrs,
count(case when xb ='2' then jsb1.zgh end) nvrs,
count(jsb3.zgh) nan_nv_rs,
t.yxmc
from dm_yxb t
left join (select zgh, xb, xydm
from sz_jsxxb
where xb in ('1','2')
and xydm is not null) jsb1
on t.yxdm = jsb1.xydm
left join (select zgh, xb, xydm
from sz_jsxxb
where xb != '1'
and xb != '2'
and xydm is not null) jsb3
on t.yxdm = jsb3.xydm
group by t.yxmc
sys_guid(),
count(case when jsb1.xb='1' then jsb1.zgh end) nanrs,
count(case when jsb1.xb='2' then jsb1.zgh end) nvrs,
count(case when jsb1.xb not in ('1','2') then jsb1.zgh end) nan_nv_rs,
t.yxmc
from dm_yxb t
left join sz_jsxxb jsb1 on t.yxdm=jsb1.xydm
where jsb1.xydm is not null
group by t.yxmc;
另外你问为什么会重复,其实很简单。
对于某个yxdm,假设为1
jsb1 存在2条记录(1,'a'),(1,'b'),sb2中存在3条记录(1,'c'),(1,'d'),(1,'e')。
那么关联后机会变成6条记录,你无法再区分得到jsb1中的2和jsb2中的3的信息。
你写的这个是正确的答案。但是你这样写就可以直接取jsb1.zgh就是结果了,为啥还sum。正确结果我有,但我只是想知道我那么写为什么会重复,我真是不知道啊
其实就是看你按什么分组了,因为你的语句中left join 两个临时的语句,主表是dm_yxb,又按照主表的 yxmc分组,count数据其实是一样的。你可以找一个简单点的三个表测试下,就知道原因了