Alle rijen waarvan waarde twee keer voorkomt

[Glest]

Hey,

Ik heb een tabel met gebruikers (users). Daarin staat onder andere hun IP adress (lastIP). Ik wil nu alle gebruikers waarvan hun IP vaker voorkomt in één lijst.

Ik weet wel hoe ik alle IP adressen die vaker voorkomen moet weergeven:

SELECT lastIP
FROM users
GROUP BY lastIP
HAVING count(lastIP) > 1

Maar verder kom ik niet. Ik heb nog wat geprobeerd met een join op users maar dan kreeg ik steeds dat het groeperen na de join gebeurde, wat opzich wel logisch is.

 

[Frats]

INNER JOIN de tabel op zichzelf, match alle gebruikers op lastIP, en zoek vervolgens welke namen meer dan 1x in die lijst staan:

SELECT u1.username
FROM users u1
INNER JOIN users u2 ON u1.lastIP = u2.lastIP
GROUP BY u1.username
HAVING count(u1.username) > 1

Hoop dat het helpt

 

[Glest]

Ah juist, dat werkt perfect. Ik zat duidelijk verkeerd om te denken. Heel erg bedankt :thumb:

Ik probeerde de gegroepeerde lijst te joinen op users maar dat gaat niet (lukte mji iig niet).

 

[Glest]

Nog één vraagje. Ik geloof dat dit werkt, maar misschien hou ik ergens geen rekening mee. Klopt het dat dit nu het aantal accounts geeft dat een IP deelt met andere accounts?

Ik geloof dat DISTINCT wel nodig is aangezien de inner join anders account 1 eerst aan 2 koppelt en vervolgens 2 aan 1.

SELECT COUNT(DISTINCT u1.id) count
FROM users u1
INNER JOIN users u2
ON u1.lastIP = u2.lastIP
WHERE u1.id <> u2.id

 

[Frats]

Dat wil je toch ook? Alle accounts die meer dan 1x voorkomen?

Als account 1 en 2 hun IP delen, dan komen ze beide meer dan 1x voor.

 

[Glest]

Ehm ja dat klopt.

Maar als je bijvoorbeeld 3 accounts hebt met hetzelfde IP koppelt hij eerst 1 aan 2 en vervolgens 1 aan 3. Dus DISTINCT is wel nodig voor het tellen van de accounts (toch?).

 

[Frats]

Hmmm, zolang je de GROUP BY erin laat staan niet

Maar als je die er uit haalt, dan kun je idd ook COUNT(DISTINCT()) gebruiken.

 

[Glest]

Voor de lijst met gebruikers gebruik ik precies je voorbeeld (ongeveer dan de techniek iig), maar voor het tellen van de gebrukers kan dat niet geloof ik?

COUNT() opereeert dan op de aparte groepen en geeft per groep aan hoeveel rijen erin zitten, terwijl ik het aantal groepen moet hebben om te tellen hoeveel gebruikers een IP delen. Bij dit tellen moet wel account 1 en account 2 met dezelfde IP allebij meegeteld worden. Maar als jij weet hoe je het aantal groepen van een GROUP BY statement kunt ophalen zou dat ook kunnen. Dan is de vraag alleen nog welke query efficienter is, maar dat kan ik dan testen.

 

[Frats]

Volgensmij kun je er gewoon een COUNT overheen gooien hoor...

SELECT COUNT(u1.username)
FROM users u1
INNER JOIN users u2 ON u1.lastIP = u2.lastIP
GROUP BY u1.username
HAVING count(u1.username) > 1

Of lever dat fouten op? Ik heb jouw tabel helaas niet...

 

[Glest]

Nee, geen fouten, maar dat levert per groep op hoeveel rijen erin zitten. COUNT() is een groep functie en gebruikt dus groepen gevormd door de GROUP BY clause. Alleen als er geen GROUP BY clause is doet de functie alsof alle rijen in één groep zitten.
Als ik deze query uitvoer:

SELECT COUNT(u1.id)
FROM users u1
INNER JOIN users u2 ON u1.lastIP = u2.lastIP
GROUP BY u1.id
HAVING count(u1.id) > 1

Dan krijg ik deze result set:

COUNT( u1 . id )
4
4
25
3
2
4
2
4
4
2
2
2
2
2
4
2
2
3
2
2
2
3
2
3
2
2
2
2
2
2
2
3
3
25
2
25
2
2
3
2
2
11
2
2
2
11
2
3
2
4
25
25
2
2
25
3
25
2
25
25

Dat is dus per groep het aantal rijen in die groep, in plaats van het aantal groepen. Daarom gebruik ik voor het tellen deze query:

SELECT COUNT(DISTINCT u1.id) count
FROM users u1
INNER JOIN users u2
ON u1.lastIP = u2.lastIP
WHERE u1.id <> u2.id