SQL Server添加序号统计数据并排序
序号需要按日期和地市排序得到的序号是一个有序列的序号
WITH b AS (
SELECT
日期,
地市,
COUNT(*) AS 总卡资量,
SUM(CAST(卡资时长分钟 AS decimal(18, 6))) AS 总卡资时长
FROM HJ_NEW
GROUP BY 日期, 地市
)
SELECT
ROW_NUMBER() OVER (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
END
) AS "序号",
日期,
地市,
总卡资量,
总卡资时长,
总卡资时长 / 总卡资量 AS 平均卡资时长
FROM
(
SELECT * FROM b
UNION ALL
SELECT 日期, '广西' AS 地市, SUM(总卡资量) AS 总卡资量, SUM(总卡资时长) AS 总卡资时长
FROM b
GROUP BY 派单日期
) AS result
GROUP BY 日期, 地市, 总卡资量, 总卡资时长
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
END;