SQL 查询优化：排序字段分析与修改

{"title":"SQL 查询优化：排序字段分析与修改","description":"本文分析两条 SQL 查询语句的排序差异，并提供修改方案，使两条语句排序结果一致。","keywords":"SQL, 查询优化, 排序, SQL 语句分析, 数据库","content":"上面一条查询SQL排序与下面一条查询SQL排序的不同之处在于排序的字段不同。\n\n在上面一条查询中，排序的字段为x.C_CITID,x.C_COUID,x.C_TOWID，即按照城市ID、县区ID、乡镇ID进行排序。\n而在下面一条查询中，排序的字段为x.C_CITID,x.C_COUID,x.C_TOWID，即按照城市ID、县区ID、乡镇ID进行排序。\n\n如果想要让两条SQL排序效果相同，可以将第一条SQL的排序字段修改为x.C_CITID,x.C_COUID,x.C_TOWID。具体修改如下：\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(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 \nfrom ( 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_ANNUAL*100+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