Início / dba / Perguntas / 174903
Accepted
Andy K
Asked: 2017-05-31 04:43:02 +0800 CST

Emular contagem de linhas em 2 colunas no Access

  • 772

Tenho a seguinte tabela no Access

insira a descrição da imagem aqui

Eu tenho várias linhas em que há duplicatas quando você filtra na primeira e na segunda coluna. Eu preciso remover essas duplicatas. A melhor maneira que encontrei é usar row_number.

Eu quero emular o equivalente a esta consulta Postgres no Microsoft Access:

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

Eu dei uma olhada na internet e encontrei isso no Stack Overflow, mas não sei por onde começar:

Esta tabela é uma tabela de buffer e não tem PK. Todos os dados duplicados devem ser removidos desta tabela. Feito isso, irá para a tabela principal onde Supplier_code& Internal_referencefazem parte do PK.

Eu construí esta consulta, mas ela não alcança o que eu quero:

 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

Por favor, encontre um extrato dos meus dados abaixo

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;

O resultado desejado pode ser a solução, onde as duplicatas são removidas

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;

Ou a solução em que as duplicatas são sinalizadas

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

Não consigo criar um autoincrement na minha query pois não é uma tabela real, mais os resultados de uma query. Veja a imagem em Acesso:

insira a descrição da imagem aqui

ms-access window-functions

2 respostas

  1. IMHO você não precisa de uma função ROW_NUMBER simulada apenas para evitar duplicatas, você pode obtê-la facilmente adicionando um campo exclusivo, neste caso um campo AUTO-INCREMENT. Se você não pode torná-lo PRIMARY KEY, pelo menos defina um índice nele sem duplicatas.

    Vamos nomear este campo ID.

    Então você pode usar uma visão ou uma subconsulta que retorna MIN(ID)agrupada porSupplier_code; Internal_reference

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

    Última etapa, JUNTE-SE a esta visualização com sua consulta atual:

    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

    Configurei um pequeno projeto MS-Access com os dados fornecidos e funciona bem.

    insira a descrição da imagem aqui

    • 2
  2. Best Answer

    Uma autojunção "triangular" e agrupar por ou - o equivalente - autojunção e contagem inline deve funcionar (mas não tenho certeza se falha devido a algumas restrições de acesso):

    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

relate perguntas