34,588
社区成员
发帖
与我相关
我的任务
分享
DECLARE @data TABLE(单号 VARCHAR(20) NOT NULL, 工序 VARCHAR(10) NOT NULL,
部门编号 INT NOT NULL, 完成数量 INT NOT NULL)
INSERT @data (单号, 工序, 部门编号, 完成数量)
VALUES
('2011090065','0010',222,1500),
('2011090065','0020',223,1497),
('2011090065','0030',223,1497),
('2011090065','0040',213,1497),
('2011090065','0050',224,1497),
('2011090065','0060',224,1497),
('2011090065','0070',220,1496),
('2011090065','0080',220,1496),
('2011090065','0090',224,0 )
;WITH
list AS (
SELECT *,LEAD(部门编号,1,NULL) OVER (PARTITION BY 单号 ORDER BY 工序) NextDept FROM @data )
SELECT * FROM list WHERE list.部门编号<> list.NextDept OR list.NextDept IS NULL
SELECT [单号],[部门序号],MAX(工序),MAX(完成数量) FROM TABLE GOUP BY [单号],[部门序号]
,不过有点问题的是,[工序]字段不是数值类型