SENDER_PROVINCE 去掉该字段中的 省,市 字符 以后分组查询 select a+b,replace (SENDER_PROVINCE,'省','') SENDER_PROVINCE from( SELECT COUNT(CASE WHEN CTNR_TYPE LIKE '40%' THEN 1 ELSE NULL END)*2 AS a, COUNT(CASE WHEN CTNR_TYPE LIKE '20%' THEN 1 ELSE NULL END) AS b, replace (SENDER_PROVINCE,'省','')SENDER_PROVINCE FROM "CRE".T_CRE_WAYBILL_HEAD WHERE status = '4' AND flag = 1 AND SUBMIT_DATE BETWEEN TO_DATE('2023-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss') and TO_DATE('2023-06-06 23:59:59','yyyy-MM-dd hh24:mi:ss') GROUP BY replace (SENDER_PROVINCE,'省','')) SQL 调整
时间: 2024-01-27 11:03:58 浏览: 24
好的,根据您的要求,我调整了 SQL 语句如下:
```
SELECT
a+b,
REPLACE(REPLACE(SENDER_PROVINCE, '省', ''), '市', '') AS SENDER_PROVINCE
FROM (
SELECT
COUNT(CASE WHEN CTNR_TYPE LIKE '40%' THEN 1 ELSE NULL END) * 2 AS a,
COUNT(CASE WHEN CTNR_TYPE LIKE '20%' THEN 1 ELSE NULL END) AS b,
REPLACE(REPLACE(SENDER_PROVINCE, '省', ''), '市', '') AS SENDER_PROVINCE
FROM "CRE".T_CRE_WAYBILL_HEAD
WHERE
status = '4'
AND flag = 1
AND SUBMIT_DATE BETWEEN TO_DATE('2023-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
AND TO_DATE('2023-06-06 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
GROUP BY REPLACE(REPLACE(SENDER_PROVINCE, '省', ''), '市', '')
) SQL
```
这样调整后,会先将 `SENDER_PROVINCE` 字段中的 `省` 和 `市` 字符替换为空字符串,然后再进行分组查询。