案例报表：抓取重复生成的服务类采购订单

五六零网校查询报表案例

报表名：抓取重复生成的服务类采购订单

特点：

• ROW_NUMBER() 生成多列分组行号
• 2、由于需要保留第一位生成的订单号，所以需要按重复单号为核心，排除第一位订单号
• 3、多层语句嵌套

本案例知识点

• /多组排序代码

ROW_NUMBER() OVER (PARTITION BY T1.[U_SalNum], T2.Q ORDER BY T1.[U_SalNum]) AS LV

示例代码

SELECT *
FROM (
	SELECT T1.[U_SalNum], T0.[DocNum], T2.Q, ROW_NUMBER() OVER (PARTITION BY T1.[U_SalNum], T2.Q ORDER BY T1.[U_SalNum]) AS LV
	FROM OPOR T0
		INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
		INNER JOIN (
			SELECT T1.[U_SalNum], COUNT(T1.[U_SalNum]) AS Q
			FROM POR1 T1
			GROUP BY T1.[U_SalNum]
		) T2
		ON T2.[U_SalNum] = T1.[U_SalNum]
	WHERE T0.[DocStatus] = 'O'
		AND T0.[DocType] = 'S'
		AND T2.Q > 1
) t0
WHERE t0.lv > 1
ORDER BY T0.[U_SalNum], T0.[DocNum]