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
其中【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】执行结果：
添加行序号：ROW_NUMBER () OVER (ORDER BY A.bsm ASC) AS 序号
分组添加序号：ROW_NUMBER () OVER (PARTITION BY xzqdm ORDER BY A.bsm ASC) AS 序号
转载于:https://www.cnblogs.com/SeNaiTes/p/11540524.html
