SQL 查询排序差异分析及解决方案

{/'title/':/'SQL 查询排序差异分析及解决方案/',/'description/':/'本文分析了两个 SQL 查询语句排序结果不同的原因，并提供了使两个查询结果相同的解决方案。/',/'keywords/':/'SQL 查询，排序，分组，子查询/',/'content/':/'///'select x.C_CITID, x.C_COUID, x.C_TOWID, x.C_VILID, x.C_NATID, x.C_ID, x.C_CITNAME, x.C_COUNAME, x.C_TOWNAME, x.C_VILNAME, x.C_NAME, x.C_PROJECTNAME, sum(x.C_FINISH) AS C_FINISH, sum(x.C_NOFINISH) AS C_NOFINISH, sum(x.m9) as m9, sum(x.m1) as m1, sum(x.m2) as m2, sum(x.m3) as m3, sum(x.m4) as m4, sum(x.m5) as m5, sum(x.m6) as m6, sum(x.m7) as m7, sum(x.m8) as m8 //n from ( select n.C_CITID, n.C_COUID, n.C_TOWID, n.C_VILID, n.C_ID as C_NATID, x.C_ID, n.C_CITNAME, n.C_COUNAME, n.C_TOWNAME, n.C_VILNAME, n.C_NAME, x.C_PROJECTNAME, if(x.C_STATUS=1,1,0) as C_FINISH, if(x.C_STATUS=0,1,0) as C_NOFINISH, sum(y.m9) as m9, sum(y.m1) as m1, sum(y.m2) as m2, sum(y.m3) as m3, sum(y.m4) as m4, sum(y.m5) as m5, sum(y.m6) as m6, sum(y.m7) as m7, sum(y.m8) as m8 from db_twenty.t_project x LEFT JOIN db_twenty.t_natural n on(x.R_NAT_ID = n.C_ID) LEFT join db_twenty.t_projectreports y on(x.C_ID = y.R_PROJECT_ID and y.C_ANNUAL100+y.C_PERIOD BETWEEN 201909 and 202304 and y.C_DELETED = 0 ) where x.C_TOWID = 441424137000 and x.C_AUDITSTATUS = 1 and x.C_DELETED = 0 GROUP BY x.C_ID ) x GROUP BY x.C_VILID ORDER BY x.C_CITID,x.C_COUID,x.C_TOWID //n//n//n select x.C_CITID, x.C_COUID, x.C_TOWID, x.C_VILID, x.C_NATID, x.C_ID, x.C_CITNAME, x.C_COUNAME, x.C_TOWNAME, x.C_VILNAME, x.C_NAME, x.C_PROJECTNAME, sum(x.C_FINISH), sum(x.C_NOFINISH), sum(x.m9) as m9, sum(x.m1) as m1, sum(x.m2) as m2, sum(x.m3) as m3, sum(x.m4) as m4, sum(x.m5) as m5, sum(x.m6) as m6, sum(x.m7) as m7, sum(x.m8) as m8 //n from ( select n.C_CITID, n.C_COUID, n.C_TOWID, n.C_VILID, n.C_ID as C_NATID, x.C_ID, n.C_CITNAME, n.C_COUNAME, n.C_TOWNAME, n.C_VILNAME, n.C_NAME, x.C_PROJECTNAME, if(x.C_STATUS=1,1,0) as C_FINISH, if(x.C_STATUS=0,1,0) as C_NOFINISH, sum(y.m9) as m9, sum(y.m1) as m1, sum(y.m2) as m2, sum(y.m3) as m3, sum(y.m4) as m4, sum(y.m5) as m5, sum(y.m6) as m6, sum(y.m7) as m7, sum(y.m8) as m8 from db_twenty.t_project x LEFT JOIN db_twenty.t_natural n on(x.R_NAT_ID = n.C_ID) LEFT join db_twenty.t_projectreports y on(x.C_ID = y.R_PROJECT_ID and y.C_ANNUAL100+y.C_PERIOD BETWEEN 201909 and 202304 and y.C_DELETED = 0 ) where x.C_TOWID = 441424137000 and x.C_AUDITSTATUS = 1 and x.C_DELETED = 0 GROUP BY x.C_ID ) x GROUP BY x.C_VILID ORDER BY x.C_CITID,x.C_COUID,x.C_TOWID //n上面一条查询SQL排序与下面一条查询SQL排序为什么不同,需要效果相同怎么处理内容：上面一条查询SQL使用了子查询，并在子查询中进行了分组操作，然后在外部查询中再次进行了分组操作。而下面一条查询SQL只在外部查询中进行了分组操作。//n//n要使两个查询的效果相同，可以将下面一条查询SQL修改如下：//n//nsql//nselect x.C_CITID, x.C_COUID, x.C_TOWID, x.C_VILID, x.C_NATID, x.C_ID, x.C_CITNAME, x.C_COUNAME, x.C_TOWNAME, x.C_VILNAME, x.C_NAME, x.C_PROJECTNAME, sum(if(x.C_STATUS=1,1,0)) as C_FINISH, sum(if(x.C_STATUS=0,1,0)) as C_NOFINISH, sum(y.m9) as m9, sum(y.m1) as m1, sum(y.m2) as m2, sum(y.m3) as m3, sum(y.m4) as m4, sum(y.m5) as m5, sum(y.m6) as m6, sum(y.m7) as m7, sum(y.m8) as m8 //nfrom db_twenty.t_project x //nLEFT JOIN db_twenty.t_natural n on x.R_NAT_ID = n.C_ID//nLEFT join db_twenty.t_projectreports y on x.C_ID = y.R_PROJECT_ID and y.C_ANNUAL*100+y.C_PERIOD BETWEEN 201909 and 202304 and y.C_DELETED = 0//nwhere x.C_TOWID = 441424137000 and x.C_AUDITSTATUS = 1 and x.C_DELETED = 0 //nGROUP BY x.C_CITID, x.C_COUID, x.C_TOWID, x.C_VILID//nORDER BY x.C_CITID, x.C_COUID, x.C_TOWID//n//n//n这样修改后，下面一条查询SQL与上面一条查询SQL的排序效果将会相同。/