记录一次上次遇到的问题

• Sql的连接查询

　　　　遇到的问题,，有一次写统计的时候遇到的连接问题。

　　　　

select (case when U.ywwd = '3406401000' then 'Test1' 
                        when U.ywwd = '3406402000' then 'Test2'
                        when U.ywwd = '3413000100' then 'Test3' 
                        when U.ywwd = '3413000200' then 'Test4' 
                        when U.ywwd = '3413000300' then 'Test5'
                        when U.ywwd = '3413000400' then 'Test6'
                        when U.ywwd = '3413000500' then 'Test7'
                        when U.ywwd = '3413000600' then 'Test8'
                        Else '合计'
                        END) as str_centno
                        ,round(sum(T.hjhd_cnt),2) hjhd_cnt, round(sum(ISNULL(T.hjhd_val, 0)),2) hjhd_val,
                        round(sum(ISNULL(T.bj_cnt, 0)),2) bj_cnt, round(sum(ISNULL(T.bj_val, 0)),2) bj_val, 
                        round(sum(ISNULL(T.dwkh_cnt, 0)),2) dwkh_cnt, round(sum(ISNULL(T.grkh_cnt, 0)),2) grkh_cnt,
                        round(sum(ISNULL(T.tj_cnt, 0)),2) tj_cnt, round(sum(ISNULL(T.fc_cnt, 0)),2) fc_cnt,
                        round(sum(ISNULL(T.qf_cnt, 0)),2) qf_cnt, round(sum(ISNULL(T.zy_cnt, 0)),2) zy_cnt,
                        round(sum(ISNULL(T.bltz_cnt, 0)),2) bltz_cnt, round(sum(ISNULL(T.hj_cnt, 0)),2) hj_cnt
                       from DwUser U left JOIN (select
                        ywwd,
                        count(case when Matter = '汇缴' then Count ELSE 0 END) as hjhd_cnt,
                        sum(case when Matter = '汇缴' then Val ELSE 0 END) as hjhd_val,
                        sum(case when Matter = '补缴' then Count Else 0 END) as bj_cnt,
                        sum(case when Matter = '补缴' then Val ELSE 0 END) as bj_val,
                        sum(case when Matter = '单位开户' then Count Else 0 END) as dwkh_cnt,
                        sum(case when Matter = '个人开户' then Count Else 0 END) as grkh_cnt,
                        sum(case when Matter = '调基' then Count Else 0 END) as tj_cnt,
                        sum(case when Matter = '封存' then Count Else 0 END) as fc_cnt,
                        sum(case when Matter = '启封' then Count Else 0 END) as qf_cnt,
                        sum(case when Matter = '转移' then Count Else 0 END) as zy_cnt,
                        sum(case when Matter = '比例调整' then Count Else 0 END) as bltz_cnt, 
                        sum(case when Matter = '缓缴' then Count Else 0 END) as hj_cnt  
                        from GjjMatterCount 
    where Start_Time >='2010-03-01' and End_Time <'2020-04-17'
GROUP BY ywwd) T on ISNULL(U.ywwd, '') = ISNULL(T.ywwd, '')  GROUP BY U.ywwd with rollup ORDER BY U.ywwd desc　　　　

这个时候就会出现这样的情况，每一行的数据都被加了很多次。

 

 

 

 由于我这里用的sum所以他会加起来（根据ywwd分组），这里关联的是用户表的ywwd，后来发现问题了，用了左连接，

 因为User表里面有很多用户的ywwd是一样的，所以就会导致无缘无故多了好多行，然后最终展示用的sum就会导致他把这些重复的行给加起来，

 其实每个ywwd是一行的，解决方法就是直接关联这几个值

 

 

 修改过后这里直接去重，保留ywwd，这个字段，这样就不会有重复的了。