SELECT CHECK_STATUS , CASE WHEN CHECK_STATUS = '机器值未取到' THEN '机器未采集到信息' WHEN CHECK_STATUS = '未取到收件信息' THEN '无收寄信息' WHEN CHECK_STATUS IN ('称重重量不符', '未计泡', '重量不符') THEN '不合格'ELSE '合格' END AS V_JHJG FROM T_JH_JHJGB

你的查询看起来是正确的，它使用了 `CASE` 表达式来根据 `CHECK_STATUS` 的值返回不同的结果。每个 `WHEN` 子句定义了一个条件，当条件匹配时，返回相应的结果。`ELSE` 子句是可选的，用于在所有条件都不匹配时返回默认值。

以下是你的查询的示例结果：

CHECK_STATUS              | V_JHJG
------------------------- | ----------------
机器值未取到                  | 机器未采集到信息
未取到收件信息                | 无收寄信息
称重重量不符                  | 不合格
其他值                     | 合格

请注意，你的查询中的 `CHECK_STATUS` 列和 `V_JHJG` 列是并列显示的，每一行对应一个结果。如果你有其他需要或者需要进一步改进查询，请提供更多细节，我将尽力提供更准确的帮助。

相关问题

SELECT t1.org_id AS sonOrgId, t1.orgName AS sonOrgName, t1.id AS routeBasesectionId, t1.`name` AS routeBasesectionName, t1.nature, zw.billCode AS diseaseTypeSn, COUNT( t3.id ) AS num, ( CASE WHEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) WHEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) ELSE sum( CASE WHEN zw.`status` = '0' THEN zw.workNum END ) END ) AS totalAmount FROM mat_zx_section t3 JOIN base_bridge bb ON bb.id = t3.qshMainId LEFT JOIN mat_zx_workload zw ON zw.mainId = t3.id LEFT JOIN base_e_route_basesection t1 ON t3.baseRoute = t1.id LEFT JOIN main_construct t4 ON t3.constructId = t4.id -- 施工申请 LEFT JOIN main_construct_check t5 ON t5.id = t3.acceptanceId -- 施工申请验收 WHERE t5.process_status = '100' GROUP BY zw.billCode, t1.id, t1.nature, t1.org_id ORDER BY t1.org_id, t1.id, zw.billCode UNION ALL SELECT son_org_id AS sonOrgId, son_org_name AS sonOrgName, route_id AS routeBasesectionId, route_name AS routeBasesectionName, route_type AS nature, disease_type_sn AS diseaseTypeSn, num AS num, total_amount AS totalAmount FROM base_history_repair_statistics；这条sql语句执行失败

这个 SQL 语句可能会因为分号（;）导致执行失败。请尝试将分号（;）删除，然后再次执行该 SQL 语句。如下所示：

SELECT 	t1.org_id AS sonOrgId, 	t1.orgName AS sonOrgName, 	t1.id AS routeBasesectionId, 	t1.`name` AS routeBasesectionName, 	t1.nature, 	zw.billCode AS diseaseTypeSn, 	COUNT( t3.id ) AS num, 	( 	CASE 			 			WHEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) IS NOT NULL THEN 				sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END )  				WHEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) IS NOT NULL THEN 					sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) ELSE sum( CASE WHEN zw.`status` = '0' THEN zw.workNum END )  				END  				) AS totalAmount  			FROM 				mat_zx_section t3 				JOIN base_bridge bb ON bb.id = t3.qshMainId 				LEFT JOIN mat_zx_workload zw ON zw.mainId = t3.id 				LEFT JOIN base_e_route_basesection t1 ON t3.baseRoute = t1.id 				LEFT JOIN main_construct t4 ON t3.constructId = t4.id -- 施工申请 				LEFT JOIN main_construct_check t5 ON t5.id = t3.acceptanceId -- 施工申请验收 				 			WHERE 				t5.process_status = '100'  			GROUP BY 				zw.billCode, 				t1.id, 				t1.nature, 				t1.org_id  			ORDER BY 				t1.org_id, 				t1.id, 				zw.billCode UNION ALL 			SELECT 				son_org_id AS sonOrgId, 				son_org_name AS sonOrgName, 				route_id AS routeBasesectionId, 				route_name AS routeBasesectionName, 				route_type AS nature, 				disease_type_sn AS diseaseTypeSn, 				num AS num, 				total_amount AS totalAmount  		FROM 	base_history_repair_statistics

帮我优化这段sql,使得查询高效SELECT t.TELEPHONE_NUMBER, t.STATIC_IP, t.FRAME_IP, t.FRAME_ROUTE, t.FRAME_ROUTE_1, t.FRAME_ROUTE_2, CASE WHEN (t.analyze_result = '1' AND CHECK_IP_VALIDITY(FRAME_IP) = '1' and CHECK_IP_VALIDITY(FRAME_ROUTE) ='1' and CHECK_IP_VALIDITY(FRAME_ROUTE_1) ='1' and CHECK_IP_VALIDITY(FRAME_ROUTE_2) ='1' and (FRAME_IP || FRAME_ROUTE || FRAME_ROUTE_1 || FRAME_ROUTE_2 != '9999')) THEN '1' ELSE case when STATIC_IP is null then '3' else '2' end END AS analyze_result FROM ( SELECT TELEPHONE_NUMBER,STATIC_IP, NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-IP-Address=\s*([^,]+)', 1, 1, NULL, 1)),'9') FRAME_IP, NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 1, NULL, 1)),'9') FRAME_ROUTE, NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 2, NULL, 1)),'9') FRAME_ROUTE_1, NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 3, NULL, 1)),'9') FRAME_ROUTE_2, CASE WHEN REGEXP_LIKE(STATIC_IP, 'Framed-IP-Address=|Framed-Route=') THEN '1' ELSE '2' END AS analyze_result FROM TI_BROADBAND_NIB WHERE DAY_ID='20230601' AND TELEPHONE_NUMBER='07231-299142' AND UPPER(STATUS) NOT IN ('INPROGRESS', 'MARKED FOR DELETION', 'SUSPEND', 'BLOCKED DUE TO SECURITY REASONS.') ) t

你可以尝试优化这段SQL的查询效率。以下是对查询进行优化的建议：

1. 索引优化：确保TI_BROADBAND_NIB表中的DAY_ID、TELEPHONE_NUMBER和STATUS列上都有适当的索引。这将提高WHERE子句的过滤效率。

2. 减少函数调用：在子查询中，可以将CHECK_IP_VALIDITY函数的调用次数减少为一次。你可以将CHECK_IP_VALIDITY(FRAME_IP)、CHECK_IP_VALIDITY(FRAME_ROUTE)、CHECK_IP_VALIDITY(FRAME_ROUTE_1)和CHECK_IP_VALIDITY(FRAME_ROUTE_2)的结果存储到临时变量中，然后在CASE语句中使用这些变量。

3. 使用JOIN代替子查询：将子查询转换为一个JOIN操作，这样可以减少查询次数并提高效率。你可以将子查询的结果存储到一个临时表中，然后与TI_BROADBAND_NIB表进行JOIN操作。

下面是优化后的SQL代码示例：

WITH temp AS (
    SELECT TELEPHONE_NUMBER,
           STATIC_IP,
           NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-IP-Address=\s*([^,]+)', 1, 1, NULL, 1)),'9') FRAME_IP,
           NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 1, NULL, 1)),'9') FRAME_ROUTE,
           NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 2, NULL, 1)),'9') FRAME_ROUTE_1,
           NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 3, NULL, 1)),'9') FRAME_ROUTE_2,
           CASE WHEN REGEXP_LIKE(STATIC_IP, 'Framed-IP-Address=|Framed-Route=') THEN '1' ELSE '2' END AS analyze_result,
           CHECK_IP_VALIDITY(FRAME_IP) AS ip_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE) AS route_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE_1) AS route_1_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE_2) AS route_2_validity
    FROM TI_BROADBAND_NIB
    WHERE DAY_ID='20230601' AND TELEPHONE_NUMBER='07231-299142' AND UPPER(STATUS) NOT IN ('INPROGRESS', 'MARKED FOR DELETION', 'SUSPEND', 'BLOCKED DUE TO SECURITY REASONS.')
)
SELECT t.TELEPHONE_NUMBER,
       t.STATIC_IP,
       t.FRAME_IP,
       t.FRAME_ROUTE,
       t.FRAME_ROUTE_1,
       t.FRAME_ROUTE_2,
       CASE WHEN (t.analyze_result = '1' AND t.ip_validity = '1' and t.route_validity = '1' and t.route_1_validity = '1' and t.route_2_validity = '1' and (t.FRAME_IP || t.FRAME_ROUTE || t.FRAME_ROUTE_1 || t.FRAME_ROUTE_2 != '9999')) THEN '1'
            ELSE CASE WHEN t.STATIC_IP IS NULL THEN '3' ELSE '2' END
       END AS analyze_result
FROM temp t;

请注意，这只是一个示例，并不代表一定能提高查询效率。你可以根据具体情况进行调整和测试。记得在使用之前备份原始数据，并在生产环境中谨慎使用。