在 Access 中模擬 2 列的行數

我在Access中有下表

當您對第一列和第二列進行過濾時，我有許多行存在重複項。我需要刪除這些重複項。我發現最好的方法是使用row_number.

我想在 Microsoft Access 中模擬此 Postgres 查詢的等效項：

SELECT 
   "Supplier_code", 
   "Internal_reference", 
   "Supplier_reference", 
   "Family_code", 
   "Purchasing_unit", 
   "Purchasing_price",
   "Purchasing_currency",
   "Consigned",
   "Eco_order_qty",
   "Pack_order_qty",
   "Min_order_qty",
   "Min_order_value",
   "Product_grossweight",
   "Product_grosscube",
   "Leadtime_days",
   "Supplier_active",
ROW_NUMBER() OVER (PARTITION BY "Supplier_code", "Internal_reference" 
ORDER BY "Internal_reference") AS "cnt"
FROM dw.T13_RefSupplier_Mexico_ToXL

我在網上看了一下，我在 Stack Overflow 上找到了這個，但我不知道從哪裡開始：

• 在 MS Access 中實現 ROW_NUMBER / PARTITION BY

這個表是緩衝表，沒有PK。必須從該表中清除所有重複數據。完成後，它將轉到主表，其中Supplier_code&Internal_reference是 PK 的一部分。

我建立了這個查詢，但它沒有達到我想要的：

SELECT 
   t1.Supplier_code,
   t1.Internal_reference,
   t1.Supplier_reference,
   t1.Family_code,
   t1.Purchasing_unit,
   t1.Purchasing_price,
   t1.Purchasing_currency,
   t1.Consigned,
   t1.Eco_order_qty,
   t1.Pack_order_qty,
   t1.Min_order_qty,
   t1.Min_order_value,
   t1.Product_grossweight,
   t1.Product_grosscube,
   t1.Leadtime_days,
   t1.Supplier_active,
   COUNT(*) AS [Ino]
FROM 
   T13_RefSupplier_Mexico_ToXL AS t1
   INNER JOIN
   T13_RefSupplier_Mexico_ToXL AS t2
       ON t2.Supplier_code = t1.Supplier_code
           AND t2.Purchasing_price <= t1.Purchasing_price
GROUP BY
   t1.Supplier_code,
   t1.Internal_reference,
   t1.Supplier_reference,
   t1.Family_code,
   t1.Purchasing_unit,
   t1.Purchasing_price,
   t1.Purchasing_currency,
   t1.Consigned,
   t1.Eco_order_qty,
   t1.Pack_order_qty,
   t1.Min_order_qty,
   t1.Min_order_value,
   t1.Product_grossweight,
   t1.Product_grosscube,
   t1.Leadtime_days,
   t1.Supplier_active
ORDER BY 1, 2

請在下面找到我的數據摘錄

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;
T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;
T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;
T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;
T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;
T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;
T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;
T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;
T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;
T080;16-0067;;402;EA;0.47;USD;0;0;;;;;;0;1;
T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;
T900;53-2008;;391;EA;0.033;USD;0;0;;;;;;63;1;

期望的結果可以是解決方案，其中重複項被刪除

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;
   T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;
   T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;
   T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;
   T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;
   T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;
   T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;
   T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;
   T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;
   T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;

或標記重複項的解決方案

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;Duplicate
T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;1
T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;1
T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;1
T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;1
T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;1
T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;1
T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;1
T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;1
T080;16-0067;;402;EA;0.47;USD;0;0;;;;;;0;1;2
T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;1
T900;53-2008;;391;EA;0.033;USD;0;0;;;;;;63;1;2

我無法在我的查詢中創建自動增量，因為它不是一個真實的表，更多的是查詢的結果。見Access中的圖片：

“三角形”自連接和分組方式或 - 等效 - 內聯自連接和計數應該可以工作（但我不確定它是否由於某些訪問限製而失敗）：

SELECT 
   t.*,
   ( SELECT COUNT(*) 
     FROM dw.T13_RefSupplier_Mexico_ToXL AS c
     WHERE       -- PARTITION BY these 2 columns 
           c."Supplier_code" = t."Supplier_code" 
       AND c."Internal_reference" = t."Internal_reference" 
                 -- ORDER BY these 2
       AND ( c."Purchasing_currency" < t."Purchasing_currency" 
          OR (  c."Purchasing_currency" = t."Purchasing_currency" 
            AND c."Purchasing_price" <= t."Purchasing_price"
             )
           )
   ) AS cnt
FROM 
   dw.T13_RefSupplier_Mexico_ToXL AS t ;

引用自：https://dba.stackexchange.com/questions/174903