在列表中显示重复数据

Question

目标：

显示表格测试中需要删除的重复数据。

然而，如果有两个重复数据，即例如"3412 New York“。测试表中只应显示1行。

问题：

不知道该怎么做。请记住，这个表可以有一百万行。

All data from the list

table testing
Region          Category    Energy
----------------------------------
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3412 New York   food        3
3412 New York   food        3
3412 Washington coke        7
3412 california chips       20
3412 california chips       20
3412 california chips       20
3412 california chips       20





Requested result that should display data in table testing
Table testing
Region          Category    Energy
----------------------------------
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3412 New York   food        3
3412 california chips       20
3412 california chips       20
3412 california chips       20

Answer 1

查看结果

SELECT
    Region, Category, Energy
FROM
    (
    SELECT
       Region, Category, Energy,
       ROW_NUMBER() OVER (PARTITION BY Region, Category, Energy ORDER BY Region) AS rn
    FROm
       MyTable
    ) X
WHERE
   X.rn > 1

Answer 2

因为您没有主键，所以可以使用group by或distinct重新生成表：

select distinct Region, Category, Energy
into newt
from t

这将使您不必识别重复项，然后再次计算如何删除这些行。