统计的sql,排序后，查出的数据前半部分是对的，后部分顺序就乱了
select (case when sum(a.n0) is null then 0 else sum(a.n0) end) n0,(case when sum(a.n1) is null then 0 else sum(a.n1) end) n1,(case when sum(a.n2) is null then 0 else sum(a.n2) end) n2,a.yab003,(case when sum(a.n3) is null then 0 else sum(a.n3) end) n3,(case when sum(a.n4) is null then 0 else sum(a.n4) end) n4,(case when sum(a.n5) is null then 0 else sum(a.n5) end) n5,(case when sum(a.n6) is null then 0 else sum(a.n6) end) n6,(case when sum(a.ymoney) is null then 0.0 else sum(a.ymoney) end) ymoney,(case when sum(a.zmoney) is null then 0.0 else sum(a.zmoney) end)zmoneyfrom (selectcount(*) as n0,0 n1,0n2,yab003,0 n3,0 n4,0 n5, 0n6,0 ymoney,0 zmoney fromlogistics.consignmentnewwhereTO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(signtime)) <= 1 andissign='1'andeffective='0' and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,count(*) as n1,0n2,yab003,0 n3,0 n4 ,0 n5, 0 n6,0 ymoney,0zmoney fromlogistics.consignmentnewwhere YEAR(now()) =YEAR(date(signtime)) and issign='1' andeffective='0' and yab003 in('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,count(*) asn2,yab003 ,0 n3,0n4 ,0 n5, 0 n6, 0 ymoney,0 zmoney fromlogistics.consignmentnewwhere YEAR(date_sub(now(), interval 1 YEAR)) =YEAR(date(signtime))andissign='1' and effective='0' and yab003 in('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003 ,0n3,count(*) as n4 ,0 n5, 0 n6,0 ymoney,0 zmoney fromlogistics.consignmentnewwhere 1=1 and YEAR(now()) =YEAR(date(signtime)) and issign='1' andeffective='0' and MONTH(now())=MONTH(date(signtime)) and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003unionallselect 0 n0,0 n1,0n2,yab003,count(*) as n3,0 n4,0 n5, 0 n6,0ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003,0 n3,0 n4,count(*) as n5, 0 n6,0 ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' and YEAR(now()) =YEAR(date(createtime))andMONTH(now()) = MONTH(date(createtime)) andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003,0 n3,0 n4,0 n5,count(*) as n6, 0 ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' and YEAR(now()) =YEAR(date(createtime)) and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003unionallselect 0 n0,0 n1,0 n2,yab003,0 n3,0 n4,0 n5,0 n6,(case whensum(money) isnull then 0 else sum(money) end) as ymoney,0 zmoney fromlogistics.charge where 1=1 and YEAR(now()) = YEAR(date(time)) andinorout='0' and chargetype='0' and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0 n2,yab003,0 n3,0 n4,0 n5,0 n6,0ymoney,(casewhen sum(money) is null then 0 else sum(money) end) aszmoney fromlogistics.charge where YEAR(now()) = YEAR(date(time)) andinorout='0'and MONTH(now()) =MONTH(date(time)) and chargetype='0' andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003)agroup by a.yab003ORDER BY a.n3 desc
修改之后，在外面又嵌套了一层，分页数据也对了
select * from((select (case when sum(a.n0) is null then 0 else sum(a.n0) end) n0,(case when sum(a.n1) is null then 0 else sum(a.n1) end) n1,(case when sum(a.n2) is null then 0 else sum(a.n2) end) n2,a.yab003,(case when sum(a.n3) is null then 0 else sum(a.n3) end) n3,(case when sum(a.n4) is null then 0 else sum(a.n4) end) n4,(case when sum(a.n5) is null then 0 else sum(a.n5) end) n5,(case when sum(a.n6) is null then 0 else sum(a.n6) end) n6,(case when sum(a.ymoney) is null then 0.0 else sum(a.ymoney) end) ymoney,(case when sum(a.zmoney) is null then 0.0 else sum(a.zmoney) end)zmoneyfrom (selectcount(*) as n0,0 n1,0n2,yab003,0 n3,0 n4,0 n5, 0n6,0 ymoney,0 zmoney fromlogistics.consignmentnewwhereTO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(signtime)) <= 1 andissign='1'andeffective='0' and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,count(*) as n1,0n2,yab003,0 n3,0 n4 ,0 n5, 0 n6,0 ymoney,0zmoney fromlogistics.consignmentnewwhere YEAR(now()) =YEAR(date(signtime)) and issign='1' andeffective='0' and yab003 in('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,count(*) asn2,yab003 ,0 n3,0n4 ,0 n5, 0 n6, 0 ymoney,0 zmoney fromlogistics.consignmentnewwhere YEAR(date_sub(now(), interval 1 YEAR)) =YEAR(date(signtime))andissign='1' and effective='0' and yab003 in('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003 ,0n3,count(*) as n4 ,0 n5, 0 n6,0 ymoney,0 zmoney fromlogistics.consignmentnewwhere 1=1 and YEAR(now()) =YEAR(date(signtime)) and issign='1' andeffective='0' and MONTH(now())=MONTH(date(signtime)) and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003unionallselect 0 n0,0 n1,0n2,yab003,count(*) as n3,0 n4,0 n5, 0 n6,0ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003,0 n3,0 n4,count(*) as n5, 0 n6,0 ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' and YEAR(now()) =YEAR(date(createtime))andMONTH(now()) = MONTH(date(createtime)) andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0n2,yab003,0 n3,0 n4,0 n5,count(*) as n6, 0 ymoney,0 zmoney fromlogistics.consignuserWHERE effective='0' and YEAR(now()) =YEAR(date(createtime)) and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003unionallselect 0 n0,0 n1,0 n2,yab003,0 n3,0 n4,0 n5,0 n6,(case whensum(money) isnull then 0 else sum(money) end) as ymoney,0 zmoney fromlogistics.charge where 1=1 and YEAR(now()) = YEAR(date(time)) andinorout='0' and chargetype='0' and yab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003union allselect 0 n0,0 n1,0 n2,yab003,0 n3,0 n4,0 n5,0 n6,0ymoney,(casewhen sum(money) is null then 0 else sum(money) end) aszmoney fromlogistics.charge where YEAR(now()) = YEAR(date(time)) andinorout='0'and MONTH(now()) =MONTH(date(time)) and chargetype='0' andyab003 in ('20001','20002','20026','0','1','2','3','5','8','9','6','0','2','3','5','6','7','8','9','1','2','3','4','5','6','7','9','0','1','2','3','5','6','7','8','0','8','9','0','1','2','3','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9','0','1','2','3')group by yab003)agroup by a.yab003)) bORDER BY b.n2 desc
