主页 / dba / 问题 / 174903
Accepted
Andy K
Asked: 2017-05-31 04:43:02 +0800 CST

在 Access 中模拟 2 列的行数

  • 772

我在 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 上找到了这个，但我不确定从哪里开始：

这个表是缓冲表，没有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中的图片：

在此处输入图像描述

ms-access window-functions

2 个回答

  1. 恕我直言，您不需要模拟 ROW_NUMBER 函数来避免重复，您可以通过添加唯一字段轻松获得它，在本例中为 AUTO-INCREMENT 字段。如果你不能让它成为 PRIMARY KEY，至少在它上面设置一个没有重复的索引。

    让我们命名这个字段 ID。

    然后您可以使用返回MIN(ID)分组的视图或子查询Supplier_code; Internal_reference

    SELECT   Min(ID) as ID, Supplier_code, Internal_reference
FROM     T13_RefSupplier_Mexico_ToXL
GROUP BY Supplier_code, Internal_reference

    最后一步，将此视图与您当前的查询结合起来：

    SELECT     ...
FROM       T13_RefSupplier_Mexico_ToXL t1
INNER JOIN TheNewView nv
ON         t1.ID = nv.ID
AND        t1.Supplier_code = nv.Supplier_code
AND        t1.Internal_reference = nv.Internal_reference

    我已经使用提供的数据建立了一个小型 MS-Access 项目，并且运行良好。

    在此处输入图像描述

    • 2
  2. Best Answer

    “三角”自连接和分组或 - 等效 - 内联自连接和计数应该有效（但我不确定它是否由于某些访问限制而失败）：

    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 ;
    • 2

相关问题