记录两个SQL：查询多个字段有相同并保留ID最大的一条，PARTITION BY先分组后排序

• 查询多个字段重复的sql：使用having count(1) > 1

SELECT *
FROM best_match a
WHERE (a.country, a.match_type, a.match_keywords) IN (
		SELECT country, match_type, match_keywords
		FROM best_match
		GROUP BY country, match_type, match_keywords
		HAVING COUNT(*) > 1
	)
	AND id NOT IN (
		SELECT MAX(id)
		FROM best_match
		GROUP BY country, match_type, match_keywords
		HAVING COUNT(*) > 1
	)

PARTITION BY先分组后排序:

SELECT c.keyword,
        c.type,
        c.clickCount
FROM 
    (SELECT b.keyword,
        b.type,
        b.clickCount,
        b.res
    FROM 
        (SELECT keyword,
         type,
         clickCount,
         row_number()
            OVER (PARTITION BY keyword
        ORDER BY  clickCount DESC) AS res
        FROM 
            (SELECT keyword,
         type,
         COUNT(*) AS clickCount
            FROM event_tracks.search_report
            WHERE dt = '%s'
            GROUP BY  keyword, type ) a ) b
            WHERE b.res = 1 ) c
        WHERE c.type = 5
    ORDER BY  c.clickCount DESC limit 30000

Gumi-21 2022-08-11 https://www.cnblogs.com/Gumi-21/p/16575114.html