SQL每日一题(20220303)及知识点总结
题目来自:公众号【SQL数据库开发】
有这样一张表F0303,这只是其中一个单号的(我感觉语句不通顺,去掉这句话)
希望按单号+工序排序,相邻行部门相同的情况,取工序号最大的那一行记录,得到的结果如下:
测试数据
CREATE TABLE F0303 ( 单号 VARCHAR(20) NOT NULL, 工序 VARCHAR(10) NOT NULL, 部门编号 INT NOT NULL, 完成数量 INT NOT NULL ) INSERT INTO F0303 VALUES('2021090065','0010',222,1500); INSERT INTO F0303 VALUES('2021090065','0020',223,1497); INSERT INTO F0303 VALUES('2021090065','0030',223,1497); INSERT INTO F0303 VALUES('2021090065','0040',213,1497); INSERT INTO F0303 VALUES('2021090065','0050',224,1497); INSERT INTO F0303 VALUES('2021090065','0060',224,1497); INSERT INTO F0303 VALUES('2021090065','0070',220,1496); INSERT INTO F0303 VALUES('2021090065','0080',220,1496); INSERT INTO F0303 VALUES('2021090065','0090',224,0);
答案
WITH tmpList AS ( SELECT *, LEAD(部门编号,1,NULL) OVER (PARTITION BY 单号 ORDER BY 工序) AS nextDp FROM F0303 ) SELECT 单号,工序,部门编号,完成数量 FROM tmpList WHERE 部门编号 <> nextDp OR nextDp IS NULL;
知识点