SQL Server排序剔除其一

最新推荐文章于 2024-06-13 14:29:56 发布

IT-例子

最新推荐文章于 2024-06-13 14:29:56 发布

阅读量571

点赞数 18

分类专栏： sql语句文章标签： sql

本文链接：https://blog.csdn.net/qq_43001132/article/details/134949661

版权

sql语句专栏收录该内容

12 篇文章 0 订阅

订阅专栏

SQL Server排序

最终效果

在这里插入图片描述

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 排名
        -- ROW_NUMBER() over(order by a.城数 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 地市 in (SELECT PX FROM PX)
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