SQL每日一题(20210604)找到当选者的名字，即选票最多的候选者

SELECT NAME FROM (
	SELECT NAME,RANK()OVER(ORDER BY NUM DESC) AS RK
	FROM (
		SELECT
			A.ID,
			A.Name,
			COUNT(*) AS NUM
		FROM T0604B B
			LEFT JOIN T0604A A ON B.CandidateID = A.ID
		GROUP BY
			A.ID,
			A.Name
		)T1
	)T2
WHERE RK = 1

WITH c AS (SELECT b.name name,
           COUNT(*) cnt
           FROM t0604b a
           LEFT JOIN t0604a b
           ON a.CandidateID = b.ID
           GROUP BY b.name)
SELECT name
FROM c
WHERE cnt = (SELECT MAX(cnt) FROM c)