SELECT CASE WHEN CTNR_TYPE LIKE '40%' THEN 1 ELSE 0 END as a, CASE WHEN CTNR_TYPE LIKE '20%' THEN 1 ELSE 0 END as b FROM "CRE".T_CRE_WAYBILL_HEAD WHERE status = '4' AND flag = 1 AND trunc(CREATE_DATE,'mm') = to_date('2022-10', 'yyyy-MM') 计算出 a =1 和 b 等于1 的个数分别是
时间: 2024-01-28 18:03:36 浏览: 25
根据您提供的 SQL 语句,可以使用以下查询语句来计算 a=1 和 b=1 的个数:
```
SELECT COUNT(CASE WHEN CTNR_TYPE LIKE '40%' THEN 1 ELSE NULL END) AS a_count,
COUNT(CASE WHEN CTNR_TYPE LIKE '20%' THEN 1 ELSE NULL END) AS b_count
FROM "CRE".T_CRE_WAYBILL_HEAD
WHERE status = '4' AND flag = 1 AND trunc(CREATE_DATE, 'mm') = to_date('2022-10', 'yyyy-MM');
```
该查询语句使用了两个 COUNT 函数,分别对满足条件的 a 和 b 的个数进行计数。其中,CASE WHEN 语句用于判断 CTNR_TYPE 字段是否以 '40%' 或 '20%' 开头,满足条件则返回 1,否则返回 NULL。最后,将返回 1 的个数进行计数,得到 a 和 b 的个数。
相关问题
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 调整
好的,根据您的要求,我调整了 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` 字段中的 `省` 和 `市` 字符替换为空字符串,然后再进行分组查询。
统计运单表(T_CRE_WAYBILL_HEAD)中状态为“东方丝路已确认”,并且有效标志为1,提交时间在所选年份内的数据,按月份统计40迟箱,和20迟箱 箱量。40尺箱:箱型代码(CTNR_TYPE)以“40”开头,算2标箱。 20尺箱:箱型代码以“20”开头,算1标箱。
好的,根据您的要求,可以使用以下 SQL 语句来查询数据:
```
SELECT DATE_FORMAT(submit_time, '%Y-%m') AS month,
SUM(CASE WHEN CTNR_TYPE LIKE '40%' THEN 2 ELSE 0 END) AS box_count_40,
SUM(CASE WHEN CTNR_TYPE LIKE '20%' THEN 1 ELSE 0 END) AS box_count_20
FROM T_CRE_WAYBILL_HEAD
WHERE status = '东方丝路已确认' AND valid_flag = 1 AND YEAR(submit_time) = '所选年份'
GROUP BY DATE_FORMAT(submit_time, '%Y-%m')
```
其中,需要将查询语句中的“所选年份”替换为您需要查询的年份。这条 SQL 语句会按照月份对数据进行分组,并统计每个月份的40尺箱和20尺箱的箱量,其中40尺箱算2标箱,20尺箱算1标箱。