c# - 查询小于固定数的数

Question

我的任务是找出与固定 MOQ 相比，哪些 MOQ（最小订单数量）更小或相等。起初，我使用了 while read() 的方法（使用 c#）并逐行查找。

但是当有 60k 条记录时它很慢。

现在我想一次全部更新而不是更新并逐行阅读。

这是我以前的代码：

 String cmdtxt8 = "SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL";
            OracleDataReader odrget = db.read(cmdtxt8);
            while (odrget.Read())
            {
                String cmdtxt9 = "UPDATE IR_MOQANS SET NOTE = 'GET'
                WHERE MANUF_PART  = '" + odrget.GetString(0) + "' AND MATERIAL = '" + odrget.GetString(1) + "' 
                AND BUOM_MOQ  
                <= 
                CAST ( (
                SELECT BUOM_MOQ FROM IR_MOQANS 
                WHERE MANUF_PART  = '" + odrget.GetString(0) + "' AND MATERIAL = '" + odrget.GetString(1) + "' 
                AND FIXD IS NOT NULL AND ROWNUM <= 1) 
                AS DECIMAL)";
                db.execmd(cmdtxt9);
            }

这是我尝试过但不起作用的查询（我想要这样的东西）：

UPDATE IR_MOQANS SET DELTA = 'G' 
WHERE SCALE_QTY 
<= 
(
    SELECT BUOM_MOQ  FROM IR_MOQANS WHERE (MANUF_PART, MATERIAL) 
    IN 
    (
        SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
    ) 
    AND FIXD IS NOT NULL AND ROWNUM <= 1
)
AND (MANUF_PART, MATERIAL) 
IN 
(
    SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
);

下面是我的表的一个例子：

manuf_part  |material   |fixd    |buom_moq
-------------------------------------------
A           |Pen        |X       |100      
-------------------------------------------
A           |Pen        |        |50        
-------------------------------------------
A           |Pen        |        |110     
-------------------------------------------
A           |Bag        |X       |200     
-------------------------------------------
B           |Cap        |        |20       
-------------------------------------------
B           |Cap        |X       |50       
-------------------------------------------
B           |Cap        |        |70       
-------------------------------------------

预期的结果将是这样的：

manuf_part  |material   |fixd    |buom_moq   |note
--------------------------------------------------------
A           |Pen        |X       |100        |GET
--------------------------------------------------------
A           |Pen        |        |50         |GET
--------------------------------------------------------
A           |Pen        |        |110        |
--------------------------------------------------------
A           |Bag        |X       |200        |GET
--------------------------------------------------------
B           |Cap        |        |20         |GET
--------------------------------------------------------
B           |Cap        |X       |50         |GET
--------------------------------------------------------
B           |Cap        |        |70         |
--------------------------------------------------------

Manuf_part 和 material 必须分组以获得相同的类别。例如，(A, Pen) 必须只与 (A, Pen) 比较。

标签： c#oracleperformance