优化一下这句sql , select IssuedQty '分拣数量',ScanQty '扫码数量', StandardCapacity as '标准小时产能',b.QtySumassembly as '累加BOM用量', (2*StandardCapacity*b.QtySumassembly) as 'JIT可分拣数量', * from Prod_Issued as a INNER join vwLinePlanOrderApplyDtl as b on a.ManufDopId = b.ManufDopId and a.ItemCode = b.ItemCode and a.ProductLineNo = b.ProductLineNo where BillNo ='202302200199' AND DataType > 0 AND DataType < 3 and a.ProductLineNo='M1904' and a.IsJITMaterial = 1
时间: 2023-03-15 19:01:14 浏览: 75
优化后的SQL语句如下:SELECT IssuedQty 分拣数量, ScanQty 扫码数量, StandardCapacity AS 标准小时产能, b.QtySumassembly AS 累加BOM用量, (2 * StandardCapacity * b.QtySumassembly) AS JIT可分拣数量 FROM Prod_Issued AS a INNER JOIN vwLinePlanOrderApplyDtl AS b ON a.ManufDopId = b.ManufDopId AND a.ItemCode = b.ItemCode AND a.ProductLineNo = b.ProductLineNo WHERE BillNo = 202302200199 AND DataType > 0 AND DataType < 3 AND a.ProductLineNo = M1904 AND a.IsJITMaterial = 1;
相关问题
delete from user where id not in ( select dt.id from ( select min(id) as id from user group by name ) dt) 解释一下这句sql
这条SQL语句的作用是从名为"user"的表中删除满足以下条件的行:对于每个名字,在该名字所对应的所有行中,只保留id最小的那一行,其他行将被删除。
具体解释如下:
1. 子查询 `(select min(id) as id from user group by name)` 返回每个名字对应的最小id值,这些值将被用于后续的筛选。
2. 外层查询 `delete from user where id not in (...)` 使用子查询返回的id值进行筛选,只保留那些id不在子查询结果中的行。
3. 因此,最终结果是删除了对于每个名字除了最小id对应的其他行,保留了每个名字对应的最小id所在的行。
SELECT itemid, MAX(clock) AS time,value FROM history GROUP BY itemid优化这条sql
您可以尝试添加item_id和clock的联合索引,这样查询时可以直接使用索引而不需要全表扫描,提高查询效率。修改后的SQL如下:
```
SELECT itemid, MAX(clock) AS time, value
FROM history
GROUP BY itemid
ORDER BY NULL;
```
注意,由于我们不需要对结果进行排序,所以可以加上`ORDER BY NULL`来避免额外的排序操作。