选择重复项

fiei3ece 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(269)

我有一个3列的表，如下面的示例。
所有行都有一个唯一的productid，但customerid和productname同时存在重复项。我只想选择每个副本的一条记录到一个包含所有3列的新表中。因此，从下面的行中，我希望将第1行和第3行放入新表中。

productid(guid)                        customerid    productname

4362C96D-B413-EA11-A811-000D3A25C7C2   12345678910   credit

C7EC397D-04BF-E611-80EE-005056A027F8   12345678910   credit

F796026C-B413-EA11-A811-000D3A25C942   24681012141   leasing

7490976F-B413-EA11-A811-000D3A25C7C6   24681012141   leasing

我使用此sql将所有重复行选择到一个新表中：

SELECT p.productid, p2.customerid, p2.productname
INTO tempTable
FROM products AS p
JOIN (SELECT customerid, productname
      FROM products 
      GROUP BY customerid, productname
      HAVING COUNT(productname)>1) AS p2
ON p.customerid = p2.customerid AND p.productname= p2.productname
ORDER BY p.customerid, p.productname

这个sql在没有productid的情况下工作，但是如果我添加的productid是唯一的pr行，就找不到重复项。

SELECT customerid, productname 
FROM testtable 
GROUP BY customerid, productname
HAVING COUNT(productname) > 1
ORDER BY customerid

| 12345678910 | credit |
| 24681012141 | leasing |

如何查询此数据以仅选择每个重复行中的一个？

sql sql-server tsql

来源：https://stackoverflow.com/questions/62029823/select-duplicates

2条答案

按热度按时间

a2mppw5e1#

CREATE TABLE MyTable (productid varchar(255),customerid bigint, productname varchar(50))
INSERT INTO MyTable (productid,customerid,productname) VALUES 
('4362C96D-B413-EA11-A811-000D3A25C7C2',12345678910,'credit'),
('C7EC397D-04BF-E611-80EE-005056A027F8',12345678910,'credit'),
('F796026C-B413-EA11-A811-000D3A25C942',24681012141,'leasing'),
('7490976F-B413-EA11-A811-000D3A25C7C6',24681012141,'leasing')

WITH CTE AS (
SELECT 
      productid,
      customerid, 
      productname,
      ROW_NUMBER() OVER (PARTITION BY customerid, productname ORDER BY productid) AS rn
    FROM MyTable 
)

SELECT customerid, 
      productname FROM CTE WHERE rn =1

GO

customerid | productname
----------: | :----------
12345678910 | credit     
24681012141 | leasing

SELECT customerid, productname 
FROM MyTable 
GROUP BY customerid, productname
HAVING COUNT(*) > 1
ORDER BY customerid 
GO

customerid | productname
----------: | :----------
12345678910 | credit     
24681012141 | leasing

db<>在这里摆弄

赞(0）回复(0）举报 2021-07-26

de90aj5v2#

您可以添加 ROW_NUMBER() 将函数窗口化到结果集以区分guid值。

SELECT 
  productid,
  customerid, 
  productname
INTO tempTable
SELECT
  productid,
  customerid, 
  productname
FROM
  (
    SELECT 
      productid,
      customerid, 
      productname,
      ROW_NUMBER() OVER (PARTITION BY customerid, productname ORDER BY productid) AS rn
    FROM testtable 
  ) AS d
WHERE d.rn = 1

赞(0）回复(0）举报 2021-07-26

我来回答

选择重复项

2条答案

相关问题

热门标签

最新问答