目录

0 场景描述

1 剩余空位区间和剩余空位编号统计分析 

2 查找已用货位区间

3 小结

---

0 场景描述

这是在做一个大型货场租赁系统时遇到的问题，在计算货场剩余存储空间时，不仅仅需要知道哪些货位是空闲的，还要能够判断出哪些货位之间是连续的。因为在新货物入场时，可以判断这些货物是否可以堆放在一起，而不是放在不连续的多个货位上，这样更便于管理，并且在出货时也更加迅速。

假设这个货场共有100个货位，现在已存放货物的货位是1、2、3、4、87、89、99、100，则剩余空位是5～86、88、90～98。数据库的设计方式一般有两种：一种是在表中为每个货位建一条记录，类似表1所示的结构设计；另一种设计方式是仅将存放有货物的货位号放在表中，也就是存货情况表中仅有货位编号列，存放1、2、3、4、87、89、99、100这几个数值。

存货情况表

货位编号	是否存放有货物（1是，0否）
1	1
2	1
3	0
... ...	... ....

相对于大型数据库而言，第一种架构设计对于查询语句编写方面会更方便一些。如果数据需要驻留在一些手持设备上，多数开发人员会更喜欢第二种架构设计，因为它能够节省宝贵的存储空间。尤其是当表的数据量非常大时，这种设计方式更能显示出它的优势。这里我们将以第二种架构设计方式来演示查询的创建方法，下面是创建示例表的语句。

create table freights as
select 1 id,1 numb
from dual
union all
select 2 id, 2 numb
from dual
union all
select 3 id,3 numb
from dual
union all
select 4 id,4 numb
from dual
union all
select 5 id,87 numb
from dual
union all
select 6 id,89 numb
from dual
union all
select 7 id,99 numb
from dual
union all
select 8 id,100 numb
from dual

 问题1：查找剩余空位区间和剩余空位编号？

 问题2：查找已用货位区间？

1 剩余空位区间和剩余空位编号统计分析 

要查找剩余空位区间，就是要找出表2所示的数值范围。 

剩余空位区间

货位开始编号	货位结束编号
5	86
88	88
90	98

 要找出这些区间的开始和结束编号，需要在间断之前的值加1，在下一组编号开始之间的值减1。例如，表中的5～86是在4的基础上加1、在87的基础上减1得来的。

可以看出，只要找出n2-n1大于1的货位组，并在n1上加1，在n2上减1，就可以得到表19-8所示的剩余空位区间。参考下面的SQL语句。 

select id,n1 + 1 f_st,n2 - 1 f_et
from (select id,numb                          n1,lead(numb) over (order by id) n2from FREIGHTS) t
where n2 - n1 > 1
;

方法2：自关联形式实现

SELECT n1.Numb + 1      f_st,MIN(n2.Numb - 1) f_et
FROM Freights n1JOIN Freights n2ON n2.Numb > n1.Numb
GROUP BY n1.Numb
HAVING (n1.Numb + 1) < MIN(n2.Numb);

 如果希望返回的不是剩余空位区间，而是剩余空位编号，则需要建立一个全部的货位编号表。下面的语句使用了递归CTE循环来建立1～100的货位编号。

WITH numbs(n) AS(SELECT 1 AS n from dualUNION ALLSELECT n + 1FROM numbsWHERE n < 100)
SELECT n
FROM numbs;

只要全部货位编号在Freights表中不存在，则表示该货位号没有使用，参考下面的语句。

WITH numbs(n) AS(SELECT 1 AS nfrom dualUNION ALLSELECT n + 1FROM numbsWHERE n < 100)
SELECT n
FROM Numbs
WHERE n NOT IN (SELECT Numb FROM Freights)

N
86
41
28
76
25
90
31
32
16
33
20
57
6
27
61
60
77
82
65
21
10
79
95
23
73
68
37
85
53
59
88
17
52
63
45
14
9
78
38
22
43
84
44
54
70
49
58
98
24
62
55
42
5
36
8
69
80
13
39
11
29
66
34
91
26
15
75
18
51
30
72
92
46
83
56
50
48
64
12
19
93
96
94
7
47
71
67
74
40
35
81
97

 如果不需要返回全部的空货位号，而是几个的话，可以使用下面的查询语句。

SELECT numb, rn, (numb - rn) AS available_Numb
FROM (SELECT Numb, ROW_NUMBER() OVER (ORDER BY numb) rnFROM Freights) t
WHERE rn <> numb

NUMB	RN	AVAILABLE_NUMB
87	5	82
89	6	83
99	7	92
100	8	92

2 查找已用货位区间

现在已经使用的货位是1、2、3、4、87、89、99、100，已用货位区间即1～4、87～87、89～89、99～100。这些区间实际上是一组连续编号中的最小值和最大值，如1～4是货位1、2、3、4中的最小值和最大值。

上面的问题实际上是经典的连续性问题，我们按照连续性问题的通用处理方法来处理

select flg, min(n1) f_st, max(n1) f_ed
from (select id,n1,n2,--当状态一致时记为0，状态变化时记为1，相同状态的累计值不变，会持续状态开始前的值。sum(case when n1 - n2 <= 1 then 0 else 1 end) over (order by id) flgfrom (select id,numb                         n1,lag(numb) over (order by id) n2from FREIGHTS) t) tgroup by flg
order by flg

FLG	F_ST	F_ED
1	1	4
2	87	87
3	89	89
4	99	100

3 小结

本文使用SQL语言分析了一种货场剩余货位统计的查询算法。

如果您觉得本文还不错，对你有帮助，那么不妨可以关注一下我的数字化建设实践之路专栏，这里的内容会更精彩。

专栏 原价99，现在活动价59.9，按照阶梯式增长，还差5个人上升到69.9，最终恢复到原价。

专栏优势：
（1）一次收费持续更新。

（2）实战中总结的SQL技巧，帮助SQLBOY 在SQL语言上有质的飞越，无论你应对业务难题及面试都会游刃有余【全网唯一讲SQL实战技巧，方法独特】

SQL很简单，可你却写不好？每天一点点，收获不止一点点-CSDN博客

（3）实战中数仓建模技巧总结，让你认识不一样的数仓。【数据建模+业务建模，不一样的认知体系】（如果只懂数据建模而不懂业务建模，数仓体系认知是不全面的）

（4）数字化建设当中遇到难题解决思路及问题思考。

我的专栏具体链接如下：

 数字化建设通关指南_莫叫石榴姐的博客-CSDN博客