使用 CTE - Code Recipes

检索数据 / 检索重复行

WITH DuplicateCity AS (
    SELECT Name
    FROM city
    GROUP BY Name
    HAVING COUNT(Name) > 1
)

SELECT *
FROM city
WHERE Name IN (
    SELECT Name
    FROM DuplicateCity
)
ORDER BY Name

ID	Name	CountryCode	District	Population
3,960	Alexandria	USA	Virginia	128,283
609	Alexandria	EGY	Aleksandria	3,328,196
1967	Anyang	CHN	Henan	420,332
2341	Anyang	KOR	Kyonggi	591,106
3,909	Arlington	USA	Virginia	174,838
3,845	Arlington	USA	Texas	332,969
3,853	Aurora	USA	Colorado	276,393
3,941	Aurora	USA	Illinois	142,990
3,546	Barcelona	VEN	Anzoátegui	322,267
654	Barcelona	ESP	Katalonia	1,503,451
...

公用表表达式 (CTE) 是把复杂查询拆成几个查询的简便办法，写法更灵活，也更好维护。这个查询与用 IN 的例子类似：先把选出重名城市的子查询放进临时表，主查询再用这张表。