Zdrojové kódy pro vývojáře.
Přeskočit odkazy pro navigaci Top 10 přispěvatelů
UživatelČlánky
codeshare45
sochor1
stoupa1
tomas.oplt15
Článek: Jak najít duplicitní hodnoty v databázové tabulce
Špatný Super
Autor:
Vytvořeno:
Popularita:

Let‘s have table Customers which is displayed below. How to find duplicate values in field CompanyName?
We can choose from many options:

 

CustomerID CompanyName
1 Microsoft
2 IBM
3 IBM

 

This query is using agregate function.

SELECT CompanyName, COUNT(CompanyName) FROM Customers GROUP BY CompanyName 
HAVING Count(*)>1

This query is using agregate function and is nearly same like previous previous method. The only difference is that previous result is not editable (you can't modify data) while this result you can modify.

SELECT CustomerID,CompanyName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM 
Customers GROUP BY CompanyName HAVING COUNT(*) > 1 )
In this example SELF JOIN is used. Each record from table one is joined with record from table two by CustomerID. In the next step values in field CompanyName are compared.  Result of this quesry is editable.
SELECT distinct Customers1.CustomerID, Customers1.CompanyName FROM Customers AS 
Customers1, Customers AS Customers2 WHERE Customers1.CustomerID = 
Customers2.CustomerID AND Customers1.CompanyName <> Customers2.CompanyName

The last example is using also agregate function in subquery. Notice that subquery (Customer2) refers to table Customer1 in the main query. 

SELECT Customers1.CustomerID, Customers1.CompanyName FROM Customers as 
Customers1 WHERE EXISTS( Select Customers2.CustomerID, Customers2.CompanyName 
FROM Customers as Customers2 WHERE Customers1.CustomerID = Customers2.CustomerID 
GROUP BY Customers2.CustomerID HAVING COUNT(*) > 1)
  Na stránku 
screen  Nový příspěvek
Název  Uživatel  Datum 
Poslední návštěva: 14:40:29, 21. listopadu 2017 První  Předchozí  0 Záznamů  Další  Poslední  

Autor článku
Jméno
Pracovní pozice
Informace
Foto

   

Počet návštěvníků:50
 
  Kontakt