我有一个带有整数ID列的表。我想得到这个列的最小未使用值。查询应该找到表ID中的第一个洞,并得到里面的最小值。我将尝试用一些例子来解释它。
示例1:无孔表
在本例中,我有一个没有洞的表,查询应该只获得最小未使用值:应获得:4
|id|
|1 |
|2 |
|3 |
示例2:顶部有孔的table
在本例中,顶部有一个洞(缺失值:1).查询找到洞并获得洞内的最小值:应该得到1。
|id|
|2 |
|3 |
|4 |
同样在本例中,顶部有一个洞,但洞内有更多缺失值(缺失值:1和2)。查询查找孔并获得孔内的最小值:应该得到1。
|id|
|3 |
|4 |
|5 |
示例3:中间有孔的table
在本例中,我们在中间有一个洞(缺失值:2和3)。查询找到洞并获得洞内的最小值:应该是2
|id|
|1 |
|4 |
|5 |
示例4:顶部和中间有孔的table
在本例中,我们有多个孔:一个在顶部(缺失值:1)和中间一个(缺失值:3).查询找到第一个洞,并得到它里面的最小值:应该得到1。
|id|
|2 |
|4 |
|6 |
我已经尝试了this post中提出的解决方案,但在我的情况下,它并不像预期的那样工作。
5条答案
按热度按时间jxct1oxe1#
kqqjbcuj2#
使用join而不是EXISTS的方法略有不同:-
使用子查询的任何解决方案的缺点是它们不太可能使用任何索引
ql3eal8s3#
你可以创建一个只有数字的表。我在下面的查询中模拟这个表。然后你可以左连接这个表。
yv5phkfx4#
编辑日期:2022年12月13日:总结:为了获得最佳性能,SQL需要无连接,无联合。这就是为什么会出现下面的解决方案。
我首先考虑过像在其他排名答案中那样使用join,但发现找不到真正的最小未使用id,例如,
3、5、6应该得到1作为最小的未使用ID,但是它们的结果是4。
另一个问题是,当列来自子查询时,我不想再次复制子查询来连接它自己,
所以我想出了另一种方法来获得真正的最小未使用ID。
假设id〉0并且是唯一的。
结果(最小未使用ID)为
请替换
unique_ids
子查询。检测的其他组合:
注意,如果
unique_ids
子查询中没有任何记录,则表示结果为1。说明:
将获取上一条记录的列值。请参阅https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag。
将获取下一条记录的列值。请参见https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead。
意志输出
| 上一个标识|身份证|下一个标识|
| - ------|- ------|- ------|
| 零|第二章|四个|
| 第二章|四个|五个|
| 四个|五个|七|
| 五个|七|零|
你可以看到我们想要的是
id != prev_id+1
或next_id != id + 1
的第一个id(将空的prev_id视为0,将空的next_id视为0)。您可以将以上所有SQL复制到SQL fiddle https://www.db-fiddle.com/中进行尝试。
该解决方案的另一个好处是它可以充分利用索引,
然后添加测试数据:
然后让我们看看实际的sql
结果是32767,时间成本是90毫秒(在MacBook Pro上的Docker容器中)。
执行计划显示它使用索引
uniq_idx_t_type_seq
,order by sequence
不需要任何开销,因为索引自然是按此顺序排序的。上述查询可以很容易地移植到其他类型的数据库,因为窗口功能,如滞后/领先是常见的,在今天。
编辑:还有其他更简单的查询,比如像一位同事建议的那样,使用MySQL的Sequence Storage,
它更优雅,性能应该也不错。
pgvzfuti5#
如果在其他表中有从1到n的值,比如t2,那么通过简单地检查
你会得到答案的