for - sql show duplicate rows




在SQL表中查找重複的值 (16)

使用一個字段很容易找到duplicates

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

所以,如果我們有一張桌子

ID   NAME   EMAIL
1    John   [email protected]
2    Sam    [email protected]
3    Tom    [email protected]
4    Bob    [email protected]
5    Tom    [email protected]

這個查詢將給我們約翰,薩姆,湯姆,湯姆,因為他們都有相同的email

但是,我想要的是使用相同的emailname獲取重複email

也就是說,我想得到“湯姆”,“湯姆”。

我需要這個原因:我犯了一個錯誤,並允許插入重複的nameemail值。 現在我需要刪除/更改重複項,所以我需要先找到它們。


SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;


SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;

我認為這將正常工作,以搜索特定列中的重複值。


嘗試以下操作:

SELECT * FROM
(
    SELECT Id, Name, Age, Comments, Row_Number() OVER(PARTITION BY Name, Age ORDER By Name)
        AS Rank 
        FROM Customers
) AS B WHERE Rank>1

嘗試這個:

SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )

如果你想刪除重複項,這裡有一個更簡單的方法來做到這一點,而不必將偶數/奇數行找到三重子選擇:

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

如此刪除:

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

更容易閱讀和理解恕我直言

注意:唯一的問題是必須執行請求,直到沒有行被刪除,因為每次只刪除每個副本中的一個


如果你想查看表中是否有重複的行,我使用下面的查詢:

create table my_table(id int, name varchar(100), email varchar(100));

insert into my_table values (1, 'shekh', '[email protected]');
insert into my_table values (1, 'shekh', '[email protected]');
insert into my_table values (2, 'Aman', '[email protected]');
insert into my_table values (3, 'Tom', '[email protected]');
insert into my_table values (4, 'Raj', '[email protected]');


Select COUNT(1) As Total_Rows from my_table 
Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc 

如果您想查找重複的數據(通過一個或多個標準)並選擇實際的行。

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/


我們如何計算重複值 要么重複2次,要么重於2次。只要數它們,不要群體明智。

像...一樣簡單

select COUNT(distinct col_01) from Table_01

試試這個代碼

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

這也應該可以,也許可以試試。

  Select * from Users a
            where EXISTS (Select * from Users b 
                where (     a.name = b.name 
                        OR  a.email = b.email)
                     and a.ID != b.id)

尤其適合您的情況如果您搜索的郵件中含有某種前綴或一般更改(例如新域名)的重複項。 那麼你可以在這些列上使用replace()


這是我提出的簡單的事情。 它使用公用表表達式(CTE)和分區窗口(我認為這些功能在SQL 2008及更高版本中)。

這個例子找到所有重複名稱和dob的學生。 您要檢查重複的字段進入OVER子句。 您可以在投影中包含您想要的其他任何字段。

with cte (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

 SELECT name, email 
    FROM users
    WHERE email in
    (SELECT email FROM users
    GROUP BY email 
    HAVING COUNT(*)>1)

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

只需在兩列上分組即可。

注意:ANSI標準將在GROUP BY中包含所有非聚合列。 MySQL允許你避免這種情況,但結果是不可預測的:

在MySQL中,你需要sql_mode=only_full_group_by


SELECT
  FirstName, LastName, MobileNo, COUNT(1) as CNT 
FROM        
  CUSTOMER
GROUP BY
  FirstName, LastName, MobileNo 
HAVING
  COUNT(1) > 1;

select id,name,COUNT(*) from India group by Id,Name having COUNT(*)>1

select name, email
, case 
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users




duplicates