22,210
社区成员
发帖
与我相关
我的任务
分享
WITH a AS (-- 单个校验员101的校验明细
SELECT *
FROM 校验记录
WHERE 校验ID = 101
)
,b AS (-- 每物品每工序的校验人数
SELECT 物品ID,
工序ID,
COUNT(*) 人数
FROM 校验记录
GROUP BY 物品ID,工序ID
)
,c AS (
SELECT a.物品ID,
a.工序ID,
1.0/b.人数 分数
FROM a
JOIN b
ON a.物品ID = b.物品ID
AND a.工序ID = b.工序ID
)
SELECT 物品ID,
ISNULL([1],0) AS 输入,
ISNULL([2],0) AS 入库,
ISNULL([3],0) AS 受入,
ISNULL([4],0) AS 包装
FROM c
PIVOT (SUM(分数)
FOR 工序ID IN ([1],[2],[3],[4])
) p