在SQL Server表中查找重复 [英] Finding duplicate in SQL Server Table

问题描述

我有一张表

+--------+--------+--------+--------+--------+
| Market | Sales1 | Sales2 | Sales3 | Sales4 |
+--------+--------+--------+--------+--------+
|     68 |      1 |      2 |      3 |      4 |
|    630 |      5 |      3 |      7 |      8 |
|    190 |      9 |     10 |     11 |     12 |
+--------+--------+--------+--------+--------+

我想在所有上述销售字段之间找到重复项。在上面的例子中，市场68和630的重要销售价值为3。

I want to find duplicates between all the above sales fields. In above example markets 68 and 630 have a duplicate Sales value that is 3.

我的问题是显示市场有重复的销售。

My problem is displaying the Market having duplicate sales.

推荐答案

如果您正式化表格，这个问题将难以解决。

This problem would be incredibly simple to solve if you normalised your table.

然后你只需要列 Market！销售，或者如果1，2，3，4重要，您可以有市场|季度|销售（或某些其他相关列名称）。

Then you would just have the columns Market | Sales, or if the 1, 2, 3, 4 are important you could have Market | Quarter | Sales (or some other relevant column name).

鉴于您的表格不是这种格式，您可以使用CTE这样做，然后从中选择，例如

Given that your table isn't in this format, you could use a CTE to make it so and then select from it, e.g.

WITH cte AS (
    SELECT Market, Sales1 AS Sales FROM MarketSales
    UNION ALL 
    SELECT Market, Sales2 FROM MarketSales
    UNION ALL 
    SELECT Market, Sales3 FROM MarketSales
    UNION ALL 
    SELECT Market, Sales2 FROM MarketSales
)
SELECT a.Market
      ,b.Market
FROM cte a
INNER JOIN cte b ON b.Market > a.Market
WHERE a.Sales = b.Sales

你可以轻松地做到这一点，没有CTE，您只需要一个大的where子句比较销售列的所有组合。

You can easily do this without the CTE, you just need a big where clause comparing all the combinations of Sales columns.

这篇关于在SQL Server表中查找重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！