SELECT a.project_name AS projectName, a.order_no AS orderNo, workHourPlan AS workHourPlan FROM t_project_plan_management a WHERE a.reserve3 != '1' workHourPlan IN ( SELECT SUM(TRUNCATE( b.work_hour , 2 ) ) FROM t_work_hour b WHERE b.order_no = a.order_no )错在哪

在这个 SQL 语句中，您的 WHERE 子句中缺少一个逻辑运算符。应该在 `a.reserve3 != '1'` 之后添加 `AND` 连接符。

这是修改后的 SQL 语句：

SELECT 
    a.project_name AS projectName, 
    a.order_no AS orderNo, 
    workHourPlan AS workHourPlan 
FROM 
    t_project_plan_management a 
WHERE 
    a.reserve3 != '1' 
    AND workHourPlan IN (
        SELECT 
            SUM(TRUNCATE(b.work_hour, 2)) 
        FROM 
            t_work_hour b 
        WHERE 
            b.order_no = a.order_no
    )

这样修改后，SQL 语句就可以正确执行了。

相关问题

为什么下面的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 语句）。另外，还可以检查查询条件是否合理，是否需要增加更多的条件来过滤数据。

帮我优化一下这个sql select e.id, e.mobile, e.encoded, CASE e.is_echo WHEN 1 THEN '已回传' else '未回传' END AS isEcho , e.order_no AS orderNo, e.pay_amount AS payAmount, e.operator_id AS operatorId, e.operator_name AS operatorName, e.operator_time AS operatorTime, e.remarks AS remarks, e.`status`, CASE p.pay_status WHEN 1 THEN '支付成功' WHEN 2 THEN '支付失败' END AS payStatus , CASE e.status WHEN 1 THEN '待支付' WHEN 2 THEN '已支付' WHEN 3 THEN '已退款' WHEN 4 THEN '订单关闭' WHEN 5 THEN '退款中' WHEN 6 THEN '退款关闭' END AS statusName, e.create_time AS createTime, u.id AS userId, p.pay_channel AS payChannel, CASE p.pay_channel WHEN 1 THEN '支付宝' WHEN 2 THEN '微信' END AS payChannelName , p.out_trade_no AS outTradeNo, e.third_party_channel AS thirdPartyChannel, info.return_amount AS returnAmount, info.return_phone AS returnPhone, info.return_name AS returnName from equity.equity_order_info e left join equity.user_info u on e.mobile = u.user_photo LEFT JOIN ( SELECT * FROM equity.pay_message GROUP BY order_no ) AS p ON p.order_no = e.order_no left join equity.refund_info AS info ON info.order_no=e.order_no <where> <if test="mobile != null and mobile != ''">and e.mobile = #{mobile}</if> <if test="orderNo != null and orderNo != ''">and e.order_no = #{orderNo}</if> <if test="payAmount != null ">and e.pay_amount = #{payAmount}</if> <if test="thirdPartyChannel != null and thirdPartyChannel != ''">and e.third_party_channel = #{thirdPartyChannel} </if> <if test="outTradeNo != null and outTradeNo != ''">and p.out_trade_no = #{outTradeNo} </if> <if test="startTime != null and startTime != '' and endTime != null and endTime != ''"> and DATE_FORMAT(e.create_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startTime},'%Y-%m-%d') AND DATE_FORMAT(#{endTime},'%Y-%m-%d') </if> <if test="status != null and status > 0">and e.status = #{status}</if> <if test="userId != null ">and u.id = #{userId}</if> </where> ORDER BY e.create_time DESC

#{mobile}</if>
<if test="startTime != null">and e.create_time &gt;= #{startTime}</if>
<if test="endTime != null">and e.create_time &lt;= #{endTime}</if>
<if test="status != null">and e.`status` = #{status}</if>
<if test="payStatus != null">and p.pay_status = #{payStatus}</if>
<if test="payChannel != null">and p.pay_channel = #{payChannel}</if>
</where>
ORDER BY e.create_time DESC, e.id DESC
LIMIT #{start},#{length}</code></pre>

优化建议：
1. 尽可能避免使用SELECT *
2. group by 关键字比其他的聚合函数效率低，尽量避免使用
3. 减少使用CASE WHEN，可以使用join来加快速度
4. 尽量减少WHERE子句中的嵌套IF条件，可以使用join来加快速度
5. 为需要检索的列创建索引，如E.mobile、P.pay_channel、E.`status`等