SQL Server排序
最终效果
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c929e4c3f1960ee8bf6215a53a32d71a.png)
sql实现
with table_a as (
SELECT *
from View_ZJCUM
WHERE id in (SELECT max(id) from ZJ_PDCUM GROUP BY 号码)
AND CAST([时间] AS date) like '2023-12%'
),
table_b as (
SELECT 市,
COUNT(*) as 派量,
SUM(CASE when 区域='城市' then 1 else 0 END) as 城数,
SUM(CASE when 区域='农村' then 1 else 0 END) as 农数
from table_a
GROUP BY 市
),
table_x as (
SELECT a.*,
dense_rank() over(order by 城数 desc) AS 排名
FROM table_b a
WHERE a.市 != '全区'
)
SELECT *
from
(
SELECT * from table_x
union all
SELECT '全区' as 市, sum(派量), sum(城数), sum(农数), NULL as 排名 from table_x
) as subquery
ORDER BY
CASE
WHEN 市 = '南' THEN 1
WHEN 市 = '桂' THEN 2
WHEN 市 = '柳' THEN 3
WHEN 市 = '玉' THEN 4
WHEN 市 = '百' THEN 5
WHEN 市 = '河' THEN 6
WHEN 市 = '贵' THEN 7
WHEN 市 = '钦' THEN 8
WHEN 市 = '梧' THEN 9
WHEN 市 = '北' THEN 10
WHEN 市 = '崇' THEN 11
WHEN 市 = '来' THEN 12
WHEN 市 = '贺' THEN 13
WHEN 市 = '港' THEN 14
WHEN 市 = '全区' THEN 15
ELSE 16
END