为什么下面的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
时间: 2024-04-27 11:20:03 浏览: 10
可能是因为查询结果中有多个相同的记录,即存在多个记录的各个字段的值都相同,因此会出现重复的结果。可以使用 DISTINCT 关键字去除重复的记录。例如:SELECT DISTINCT tp.parent_production_orders AS parent_production_orders, tp.production_orders AS production_orders, ... (省略后面的 SQL 语句)。另外,还可以检查查询条件是否合理,是否需要增加更多的条件来过滤数据。
相关问题
一下SQL语句有错吗:select p.product_name , s.year , s.price from Sales as s left join Product as p where s.product_id = p.product_id;
这个 SQL 语句有误,应该在 left join 后面指定关联条件,如下所示:
```
SELECT p.product_name, s.year, s.price
FROM Sales AS s
LEFT JOIN Product AS p ON s.product_id = p.product_id;
```
在这个语句中,我们使用 ON 子句来指定关联条件,连接 Sales 表和 Product 表的 product_id 列。这样就可以正确地将每个销售记录与其对应的产品名称关联起来,同时在没有匹配产品的情况下也可以保留销售记录。
select Customers.cust_name,Orders.oder_num from Customers inner join Orders on Customers.cust_id == Orders.cust_id order by Customers.cust_name desc这句SQL有什么语法错误
这句 SQL 语句看起来是正确的,但是有一个小错误。在 SQL 中,"==" 不是用于比较相等的运算符,而是 " = "。因此,正确的 SQL 语句应该是:
```sql
SELECT Customers.cust_name, Orders.oder_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY Customers.cust_name DESC
```
这将按照顾客的名称(以降序排列)和订单号选择顾客和订单信息,并将它们结合在一起。