blob: 8909494db78894e583879546bbfa2e13040e9e1f (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
USE lastfm
/**
* Deletes all entries in badges
* for each unique username + type
* where more than one style or color exists
*/
CREATE TEMPORARY TABLE variants (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, variants.count
FROM badges, variants
WHERE badges.username = variants.username
AND ( (badges.type = variants.type AND variants.count > 1)
OR ( (badges.type LIKE concat(variants.type,"%") OR variants.type LIKE concat(badges.type,"%"))
AND badges.type != variants.type )
) ;
|
