select p.disposalname as 处置场所名称, p.area as 处置场所所属区, processingcapacity as 处置场所类型, SUM(CASE WHEN w.state = '已完成' THEN 1 ELSE 0 END) AS count_1 from unijz_unit_absorptive p join unijz_record_waybill w on p.id = w.absorptiveid where w.createtime > to_date('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');这条sql的问题在哪里

这条SQL语句的问题在于，SELECT语句中使用了“processingcapacity as 处置场所类型”，但是在FROM子句中没有将processingcapacity字段包含在查询的表中，因此无法使用该列名。另外，如果processingcapacity字段是个数字类型的列，应该使用SUM()函数对它进行聚合操作。

以下是修改后的SQL语句：

SELECT 
    p.disposalname AS 处置场所名称, 
    p.area AS 处置场所所属区, 
    SUM(CASE WHEN w.state = '已完成' THEN 1 ELSE 0 END) AS count_1,
    MAX(p.processingcapacity) AS 处置场所类型
FROM 
    unijz_unit_absorptive p 
    JOIN unijz_record_waybill w ON p.id = w.absorptiveid 
WHERE 
    w.createtime > to_date('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY 
    p.disposalname, 
    p.area

在这个修改后的SQL语句中，我们将processingcapacity列包含在了SELECT语句中，并使用了MAX()函数对其进行聚合操作。同时，我们还对SELECT子句中的列名做了一些微调。最后，我们使用GROUP BY子句对查询结果进行了分组。

相关问题

为什么下面的sql语句会输出重复的结果：SELECT tp.parent_production_orders AS parent_production_orders, tp.production_orders AS production_orders, tp.work_order AS work_order, tp.contract AS contract, tp.sbbh AS sbbh, tp.batch_num AS batch_num, tp.product_code AS product_code, tp.product_number AS product_number, tp.product_name AS product_name, to_char( middle.create_date, 'yyyy-mm-dd' ) AS issued_date, to_char( to_timestamp( tp.delivery_time / 1000 ), 'yyyy-mm-dd' ) AS delivery_time, middle.line_code AS work_area_code, middle.line_name AS work_area_name, tp.workorder_number AS workorder_number, tp.complete_number AS complete_number, tp.part_unit AS part_unit, middle.work_time_type AS work_time_type, middle.process_time AS process_time, CASE WHEN sc.totalSubmitHours IS NULL THEN 0 ELSE sc.totalSubmitHours END AS submit_work_hours, CASE WHEN middle.process_time > 0 AND sc.totalSubmitHours IS NOT NULL THEN round( ( sc.totalSubmitHours / middle.process_time ), 2 ) * 100 ELSE 0 END plan_achievement_rate, CASE WHEN sc.totalSubmitHours IS NULL THEN 0 ELSE round( CAST ( sc.totalSubmitHours AS NUMERIC ) / CAST ( 60 AS NUMERIC ), 1 ) END AS submit_work_hours_h, round( CAST ( middle.process_time AS NUMERIC ) / CAST ( 60 AS NUMERIC ), 1 ) AS process_time_h, pinfo.material_channel AS material_channel FROM hm_model_work_order_report_middle middle LEFT JOIN hm_model_trc_plan tp ON middle.work_order = tp.work_order LEFT JOIN ( SELECT oro.work_order AS orderNo, oro.work_area_code AS lineCode, SUM ( submit_work_hours ) AS totalSubmitHours, '自制' AS workHourType FROM hm_model_trc_order_report_operation_u orou LEFT JOIN hm_model_trc_order_report_operation oro ON orou.work_order_process_id = oro.ID WHERE orou.work_order_process_id IS NOT NULL AND oro.work_area_code IS NOT NULL GROUP BY oro.work_order, oro.work_area_code UNION all SELECT ohs.work_order_no AS orderNo, ohs.line_code AS lineCode, SUM ( receiving_hour ) AS totalSubmitHours, '外委' AS workHourType FROM hm_model_outsourcing_hour_statistics ohs GROUP BY ohs.work_order_no, ohs.line_code ) sc ON middle.work_order = sc.orderNo AND middle.line_code = sc.lineCode AND middle.work_time_type = sc.workHourType LEFT JOIN hm_model_part_info AS pinfo ON tp.product_number = pinfo.part_code WHERE middle.process_time > 0 AND tp.delivery_time IS NOT NULL AND tp.production_orders LIKE'FJ2023051100286' ORDER BY to_char( to_timestamp( tp.delivery_time / 1000 ), 'yyyy-mm-dd' ) DESC, tp.parent_production_orders DESC, tp.node_level ASC

可能是因为查询结果中有多个相同的记录，即存在多个记录的各个字段的值都相同，因此会出现重复的结果。可以使用 DISTINCT 关键字去除重复的记录。例如：SELECT DISTINCT tp.parent_production_orders AS parent_production_orders, tp.production_orders AS production_orders, ... （省略后面的 SQL 语句）。另外，还可以检查查询条件是否合理，是否需要增加更多的条件来过滤数据。

LEFT JOIN ( SELECT FARMER_CD AS FARMER_CD, SUM(CNTRCT_SLL_WGHT) AS CTRT_WEIGHT, SUM( CASE WHEN CNTRCT_TYPE <> 'NOR_CONT' THEN CNTRCT_SLL_WGHT ELSE 0 END ) AS CHANGE_CTRT_WEIGHT, SUM(CNTRCT_PLNT_AREA) AS CTRT_AREA FROM R_PC_CTRT_PC_D WHERE SSTC = '35262703A' AND DATA_STATE = '1' AND PLAN_TYPE = 'INPLAN' AND LEAF_TYPE_CD = '10' AND BUSINESS_YEAR = 2022 GROUP BY FARMER_CD ) B ON A.FARMER_CD = B.FARMER_CD

这是一个SQL查询语句，用于左连接两个表 A 和 B，其中 B 表是一个子查询，对 R_PC_CTRT_PC_D 表进行了聚合计算。查询条件包括 SSTC、DATA_STATE、PLAN_TYPE、LEAF_TYPE_CD 和 BUSINESS_YEAR，目的是筛选出符合条件的数据进行连接和计算。具体的计算方式包括对 B 表中的 CNTRCT_SLL_WGHT 和 CNTRCT_PLNT_AREA 字段进行求和，并对 CNTRCT_TYPE 不等于 'NOR_CONT' 的数据进行特殊处理，最终得到 CTRT_WEIGHT 和 CHANGE_CTRT_WEIGHT 两个结果字段。