时间戳

首页 > 解决方案 > SQL查询根据UNION数据中的列值更新条件

sql - SQL查询根据UNION数据中的列值更新条件

问题描述

我有 4 张不同的桌子

1. JPMaster: It is a master table that has three columns. JPCode, JPName, and CourseCode. Basically one JPCode can have different CourseCodes in it. E.g.

JPCode  JPName  CourseCode
JP001   ABC     123
JP001   ABC     124
JP002   DEF     125
JP002   DEF     124

2. CourseMaster : Master tables for course

CourseCode  CourseName
123         Course1
124         Course2
125         Course3
126         Course4

3. EmployeeJPAssignment: Table where Employee can be assigned to JP, that means all course under that JP will be assigned to that Employee.

EmpNumber   JPCode
001         JP001
002         JP002

4. EmployeeCourseCompletion: Table where course completed by an employee is listed. Please note that Employee can complete a course which may be part JP that is assigned AND employee can directly complete a course that may not be part of any JPCode. E.g.

EmpNumber   CourseCode
001         123
002         125
002         126

注意 Course 126不是 JPCode 的一部分，但员工可以直接完成可能不属于 JPCode 的课程。

例如

SELECT 
AC.EmpNumber AS employee_id
,AC.ActivityCode AS resourceId
,AC.RegistrationDate AS courseEnrollmentDate
,AC.EndDate AS courseCompletionDate
,JP.JPCode AS curriculaId
--,AC.LastUpdated AS lastUpdatedActivityCompletetion
--,AL.LastUpdated AS lastUpdatedActivityLibrary
--,CF.LastUpdated AS lastUpdatedCurrFile
--,JP.LastUpdated AS lastUpdatedJP
FROM SLTICurrFile CF
INNER JOIN SLTIJPStructure JP ON JP.JPCode = CF.CurriculaCode
Full Outer JOIN SLTIActivityCompletion AC ON AC.ActivityCode = JP.ActivityCode and AC.EmpNumber = CF.EmpNumber
INNER JOIN SLTIActivityLibrary AL ON AC.ActivityCode = AL.ActivityCode
WHERE (AC.EmpNumber = '001' )
order by AC.ActivityCode

现在它仅在分配的课程完成时显示结果，但不显示已分配但未完成的课程

但不确定如何获得结果

标签： sqlsql-server

解决方案

如果您需要在分配的课程完成时显示结果，并且单独的项目需要未分配的课程并直接完成可能不属于 JPCode 的课程。

试试这个寻找结果：

Select   EC.EmpNumber,CM.CourseName,ISNull(JM.JPName,'--') AS JPName
        ,ISNull(JM.JPCode,'CompleteDirectly') AS JPCode
From EmployeeCourseCompletion EC
    Left outer join EmployeeJPAssignment EA ON EC.EmpNumber=EA.JPCode
    Left outer join CourseMaster CM  ON CM.CourseCode = EC.CourseCode
    Left outer Join JPMaster JM ON JM.CourseCode = EC.CourseCode
Where EC.EmpNumber = 002

结果是：

EmpNumber|CourseName|JPName|JPCode
---------|----------|------|-----------
002      |Course3   |DEF   |JP002     
002      |Course4   |--    |CompleteDirectly

你的标题更新了！我在这个问题中找不到您需要的任何更新。如果您需要从此查询中更新，只需使用where JM.JPName is Null 

Select   EC.EmpNumber,CM.CourseName,JM.JPName,JM.JPCode
From EmployeeCourseCompletion EC
    Left outer join EmployeeJPAssignment EA ON EC.EmpNumber=EA.JPCode
    Left outer join CourseMaster CM  ON CM.CourseCode = EC.CourseCode
    Left outer Join JPMaster JM ON JM.CourseCode = EC.CourseCode
Where JM.JPCode is Null

如果您不需要其他课程，请使用where JM.JPCODE is not null.

我希望这个答案对你有用。

推荐阅读