报错如下:
错误sql
SELECT b.batchno, b.subtypeno, b.entity_id, b.package_spec, b.package_ratio, b.produce_date, b.validate_date, a.PACKLAYER, a.CURCODE, bc.product_name, bc.spec, bc.pack_unit, bc.authorized_no, p.product_no FROM B_P_CODE_MA4KL1QT221 a, b_p_code_rela b LEFT JOIN b_cndc_subtypeno c ON b.subtypeno = c.subtypeno LEFT JOIN b_cts_product bc ON c.cndc = bc.drug_id_code LEFT JOIN b_product p ON p.esc_subtypeno = c.subtypeno WHERE a.RELA_ID = b.rela_id AND a.ISVALID = '1' AND b.isvalid = '1' AND c.isvalid = '1' AND p.is_valid = '1' AND bc.isvalid = '1' AND a.CURCODE = 'MA4KL1QT23211026000001' GROUP BY b.rela_id
可能的原因如下:
1、GROUP BY 和 ORDER BY一起使用时,ORDER BY要在GROUP BY的后面。
2、ORDER BY 的字段在GROUP BY 后面必须有
3、在select需要查询的语句中选中的字段,必须出现在group by子句中
根据第3条,我们的sql不满足,故删除group by 语句,因为rela_id是主键,不需要根据rela_id来分组。