Mysql刷题笔记 03.01 希望按单号+工序排序,相邻行部门相同的情况,取工序号最大的那一行记录?
CREATE TABLE T0301(
单号 VARCHAR(20) NOT NULL,
工序 VARCHAR(10) NOT NULL,
部门编号 INT NOT NULL,
完成数量 INT NOT NULL
);
INSERT INTO T0301 VALUES('2011090065','0010',222,1500);
INSERT INTO T0301 VALUES('2011090065','0020',223,1497);
INSERT INTO T0301 VALUES('2011090065','0030',223,1497);
INSERT INTO T0301 VALUES('2011090065','0040',213,1497);
INSERT INTO T0301 VALUES('2011090065','0050',224,1497);
INSERT INTO T0301 VALUES('2011090065','0060',224,1497);
INSERT INTO T0301 VALUES('2011090065','0070',220,1496);
INSERT INTO T0301 VALUES('2011090065','0080',220,1496);
INSERT INTO T0301 VALUES('2011090065','0090',224,0);
T0301
+
| 单号 | 工序 | 部门编号 | 完成数量 |
+
| 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 |
+
结果
+
| 单号 | 工序 | 部门编号 | 完成数量 | NextDept |
+
| 2011090065 | 0010 | 222 | 1500 | 223 |
| 2011090065 | 0030 | 223 | 1497 | 213 |
| 2011090065 | 0040 | 213 | 1497 | 224 |
| 2011090065 | 0060 | 224 | 1497 | 220 |
| 2011090065 | 0080 | 220 | 1496 | 224 |
| 2011090065 | 0090 | 224 | 0 | NULL |
+
'''
解决方法
1.LEAD()函数是一个窗口函数,允许向后看多行并从当前行访问行的数据。
LEAD()函数对于计算同一结果集中当前行和后续行之间的差异非常有用。
2.LEAD语法:LEAD(<expression>[,offset[, default_value]]) OVER ( PARTITION BY (expr) ORDER BY (expr))
expression:LEAD()函数返回的值expression从offset-th有序分区排。
offset:offset是从当前行向后行的行数,以获取值。offset必须是一个非负整数。如果offset为零,则LEAD()函数计算expression当前行的值。如果省略 offset,则LEAD()函数默认使用一个。
default_value:如果没有后续行,则LEAD()函数返回default_value。例如,如果offset是1,则最后一行的返回值为default_value。如果您未指定default_value,则函数返回 NULL 。
PARTITION BY子句:PARTITION BY子句将结果集中的行划分LEAD()为应用函数的分区。如果PARTITION BY未指定子句,则结果集中的所有行都将被视为单个分区。
ORDER BY子句:ORDER BY子句确定LEAD()应用函数之前分区中行的顺序。
3. 举例
查找每个客户的订单日期和下一个订单日期
SELECT customerName,orderDate,
LEAD(orderDate,1) OVER(PARTITION BY customerName ORDER BY orderDate) nextOrderDate
FROM orders
结果输出:
+
| customerName | orderDate | nextOrderDate |
+
| Atelier graphique | 2013-05-20 | 2014-09-27 |
| Atelier graphique | 2014-09-27 | 2014-11-25 |
| Atelier graphique | 2014-11-25 | NULL |
| Signal Gift Stores | 2013-05-21 | 2014-08-06 |
| Signal Gift Stores | 2014-08-06 | 2014-11-29 |
| Signal Gift Stores | 2014-11-29 | NULL |
'''
CREATE VIEW v0301 AS(
SELECT *,
LEAD(部门编号,1,NULL) OVER(PARTITION BY 单号 ORDER BY 工序) NextDept
FROM T0301
)
SELECT * FROM v0301 v
WHERE v.部门编号 <> v.NextDept OR v.NextDept IS NULL