在Sql server中为null时填充自动递增INT 5位数值

问题描述

我有一个secnario，我需要自动生成一个字段的值，如果它是null的话。



Ex：表1. employeeDetails

I have a secnario where I need to auto generate a value of a field if in case if it is null.

Ex :TABLE 1. employeeDetails

empName empId  empExtension
   A      101   null
   B      102   987
   C      103   986
   D      104   null
   E      105   null

表2. employeeDepartment

TABLE 2. employeeDepartment

deptName  empId
     HR      101
     ADMIN   102
     IT      103
     IT      104
     IT      105

QUERY

QUERY

SELECT empdt.empId, empdprt.deptName, empdt.empExtension
         FROM employeeDetails empdt
       LEFT JOIN employeeDepartment empdprt
         ON empdt.empId = empdprt.empId

结果：

RESULT :

empId deptName empExtension
   101    HR          null
   102    ADMIN       987
   103    IT          986
   104    IT          null
   105    IT          null

现在我的问题是我想插入一些虚拟值，它从5位INT编号开始替换空值和自动增量



预期输出：

Now my question is I want to insert some dummy value which replaces null and auto-increments starting from a 5 digit INT number

EXPECTED OUTPUT:

empId     deptName    empExtension
      101    HR          12345
      102    ADMIN       987
      103    IT          986
      104    IT          12346
      105    IT          12347

约束：我无法更改现有的表结构或任何字段数据类型。

Constraints : I cannot change existing tables structure or any field datatype.

推荐答案

with aCTE as (
	SELECT 
		empdt.empId, 
		empdprt.deptName, 
		empdt.empExtension,
		row_number() over(partition by case when empdt.empExtension is null then 1 else 0 end order by empdt.empId) as num -- generate sequential numbers
	FROM 
		employeeDetails empdt
		LEFT JOIN employeeDepartment empdprt ON empdt.empId = empdprt.empId
		)
select 
	a.empId,
	a.deptName,
	isnull(a.empExtension,num) --replaces null empExtension with the num
from aCTE a

这篇关于在Sql server中为null时填充自动递增INT 5位数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！