MS-Sql 生成数字--取最小流水号实例

qq874655551

于 2019-07-04 10:54:04 发布

阅读量319

点赞数 1

CC 4.0 BY-SA版权

分类专栏： SQL笔记常用sql语句

本文链接：https://blog.csdn.net/wanglui1990/article/details/94599530

常用sql语句同时被 2 个专栏收录

4 篇文章

订阅专栏

SQL笔记

3 篇文章

订阅专栏

文章来源

说明：innovator.PART表LX_IMA01字段值，长度13，最后一段为4位纯数字流水号。

功能：获取最小且未在PART表LX_IMA01字段使用的流水号。

WITH                                                                                          
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),                                                  
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),                                     
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),                                     
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),                                     
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),                                     
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS num FROM L4)          --0,生成流水码
            
SELECT TOP (9999) MIN(d.num) FROM Nums AS d   -,2，从1~9999数字取最下的流水号                                                
WHERE NOT EXISTS(  --1，排除已存在的流水号                                                                           
SELECT sir.serial FROM(                                                                       
SELECT DISTINCT SUBSTRING(LX_IMA01, LEN('195-0004-') + 1, 4) AS seriaL 
FROM    innovator.PART                                                                        
WHERE   LX_IMA01 LIKE '195-0004-%'                                                  
        AND ISNUMERIC(SUBSTRING(LX_IMA01, LEN('195-0004-') + 1,4)) = 1                         							  
        AND LEN(LX_IMA01) = 13				  
    	AND LEN(LX_IMA01) - LEN('195-0004-') = 4
	) AS sir WHERE sir.serial =D.num                                                      
 )