一种可能的方法是使用
OPENJSON()默认模式解析具有ID的JSON数组，然后生成具有名称的JSON数组。在
OPENJSON()默认模式返回与列的表格
key，
value并
type与
key列保存每个项目的索引。请注意，这里重要的部分是按照与IDs
JSON数组中存在的名称相同的顺序生成名称。您需要使用基于字符串聚合的方法，因为我不认为您可以使用生成具有标量值的JSON数组
FOR JSON。
表格：
create table #subjectList(subjectID int identity(1,1),subjectName varchar(50))insert into #subjectList(subjectName)select 'Math' union allselect 'English' union allselect 'Hindi' union allselect 'PC' union allselect 'Physics'Create table #studentList(studentID int identity(1,1), subjectName varchar(50), choseSubjectList varchar(max))insert into #studentList(subjectName, choseSubjectList)Select 'A','["1","2"]' union allSelect 'B','["3","2","5"]' union allSelect 'C','["6","2"]'create table #studentWithSubject(studentID int,subjectName varchar(50),choseSubjectIDList varchar(max),choseSubjectNameList varchar(max))insert into #studentWithSubject(studentID,subjectName,choseSubjectIDList)Select a.studentID,a.studentID,a.choseSubjectListfrom #studentList a
陈述：
UPDATe #studentWithSubjectSET choseSubjectNameList = ( CONCAt( '["', STUFF( (SELECT CONCAt('","', COALESCE(s.subjectName, '')) FROM OPENJSON(#studentWithSubject.choseSubjectIDList) j LEFT JOIN #subjectList s ON j.[value] = s.subjectID ORDER BY ConVERT(int, j.[key]) FOR XML PATH('')), 1, 3, '' ), '"]' ) )结果：
studentID subjectName choseSubjectIDList choseSubjectNameList11["1","2"]["Math","English"]22["3","2","5"] ["Hindi","English","Physics"]33["6","2"]["","English"]
