看代碼吧~

WITH Name AS (
SELECT
*
FROM
(
SELECT
xzqdm,
SUBSTRING (zldwdm, 1, 9) xzdm,
COUNT (*) sl
FROM
sddltb_qc
WHERE
xzqdm IN (‘130432’, ‘210604’)
GROUP BY
xzqdm,
SUBSTRING (zldwdm, 1, 9)
) AS A
ORDER BY
xzqdm,
xzdm,
sl
) SELECT
xzqdm,
xzdm,
sl
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY xzqdm
ORDER BY
sl DESC
) AS Row_ID
FROM
Name
) AS A
WHERE
Row_ID <= 2
ORDER BY
xzqdm