mysql在 union 与group by后order by排序混乱

最新推荐文章于 2023-12-20 17:23:32 发布

qq_1970275231

最新推荐文章于 2023-12-20 17:23:32 发布

阅读量723

点赞数 1

分类专栏：项目中遇到的问题文章标签： mysql

本文链接：https://blog.csdn.net/qq_43482570/article/details/105595869

版权

在使用MySQL进行数据统计时，遇到一个奇怪的现象：在UNION和GROUP BY操作后，通过ORDER BY进行排序，数据的顺序在前半部分正确，但后半部分出现混乱。为了解决这个问题，尝试在外层添加了一层查询，并实现了正确的分页，最终得到了准确的排序结果。

摘要由CSDN通过智能技术生成

统计的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)zmoney
    from (
  select
  count(*) as n0,0 n1,0
  n2,yab003,0 n3,0 n4,0 n5, 0
  n6,0 ymoney,0 zmoney from
  logistics.consignmentnew
  where
  TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(signtime)) <= 1 and
  issign='1'
  and
  effective='0' and yab003 in ('20001','20002','20026','20030','20031','20032','20033','20035','20038','20039','20046','20050','20052','20053','20055','20056','20057','20058','20059','20061','20062','20063','20064','20065','20066','20067','20069','20070','20071','20072','20073','20085','20086','20087','20088','20090','20148','20149','20150','20151','20152','20153','20155','20156','20157','20158','20159','20160','20161','20162','20163','20164','20165','20166','20167','20168','20169','20170','20171','20172','20173','20174','20175','20176','20177','20178','20179','20180','20181','20182','20183','20184','20185','20186','20187','20188','20189','20190','20191','20192','20193')
  group by yab003
  union all
  select 0 n0,count(*) as n1,0
  n2,yab003,0 n3,0 n4 ,0 n5, 0 n6,0 ymoney,0
  zmoney from
  logistics.consignmentnew
  where YEAR(now()) =
  YEAR(date(signtime)) and issign='1' and
  effective='0' and yab003 in
  ('20001','20002','20026','20030','20031','20032','20033','20035','20038','20039','20046','20050','20052','20053','20055','20056','20057','20058','20059','20061','20062','20063','20064','20065','20066','20067','20069','20070','20071','20072','20073','20085','20086','20087','20088','20090','20148','20149','20150','20151','20152','20153','20155','20156','20157','20158','20159','20160','20161','20162','20163','20164','20165','20166','20167','20168','20169','20170','20171','20172','20173','20174','20175','20176','20177','20178','20179','20180','20181','20182','20183','20184','20185','20186','20187','20188','20189','20190','20191','20192','20193')
  group by yab003
  union all
  select 0 n0,0 n1,count(*) as
  n2,yab003 ,0 n3,0
  n4 ,0 n5, 0 n6, 0 ymoney,0 zmoney from
  logistics.consignmentnew
  where YEAR(date_sub(now(), interval 1 YEAR)) =
  YEAR(date(signtime))
  and
  issign='1' and effective='0' and yab003 in
  ('20001','20002','20026','20030','20031','20032','20033','20035','20038','20039','20046','20050','20052','20053','20055','20056','20057','20058','20059','20061','20062','20063','20064','20065','20066','20067','20069','20070','20071','20072','20073','20085','20086','20087','20088','20090','20148','20149','20150','20151','20152','20153','20155','20156','20157','20158','20159','20160','20161','20162','20163','20164','20165','20166','20167','20168','20169','20170','20171','20172','20173','20174','20175','20176','20177','20178','20179','20180','20181','20182','20183','20184','20185','20186','20187','20188','20189','20190','20191','20192','20193')
  group by yab003
  union all
  select 0 n0,0 n1,0
  n2,yab003 ,0
  n3,count(*) as n4 ,0 n5, 0 n6,0 ymoney,0 zmoney from
  logistics.consignmentnew
  where 1=1 and YEAR(now()) =
  YEAR(date(signtime)) and issign='1' and
  effective='0' and MONTH(now

最低0.47元/天解锁文章

qq_1970275231

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
mysql在 union 与group by后order by排序混乱

统计的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 ...
复制链接

扫一扫