sql - 如何修复sql中select语句中的子查询
问题描述
Public ClientEmpIDx As String = ""
Public Sub PromotionLog_table()
Dim cmd As New SqlCommand
Dim da7 As New SqlDataAdapter
Dim sSql7 As String = String.Empty
Dim sSql8 As String = String.Empty
cmd.Connection = Con_view
cmd.CommandType = CommandType.Text
sSql7 = "Select PromotionLog.EmpID as [Emp ID], Employee.ClientEmpID as [EMP No],"
sSql7 = sSql7 & "RTRIM(Employee.Initials) + + LTRIM(Employee.LastName) As [Name], "
sSql7 = " PromotionLog.OldGradeID, (Select RTRIM(Grade.GradeName) "
sSql7 = sSql7 & " FROM PromotionLog join Grade on (PromotionLog.OldGradeID= Grade.GradeID) "
sSql7 = sSql7 & " WHERE Grade.GradeID = PromotionLog.OldGradeID) "
sSql7 = sSql7 & "PromotionLog.NewGradeID as [New Grade ID],"
sSql7 = sSql7 & " RTRIM(Designation.DesName) as [New Desig], "
sSql7 = sSql7 & "PromotionLog.PromotedDate as [Promoted Date], "
sSql7 = sSql7 & "PromotionLog.SystemPromotedDate as [System Promoted Date], "
sSql7 = sSql7 & "RTRIM(PromotionLog.Reason) as [Remarks] "
sSql7 = sSql7 & "FROM Employee join PromotionLog on (Employee.EmpID= PromotionLog.EmpID) "
sSql7 = sSql7 & " join Grade on (PromotionLog.NewGradeID= Grade.GradeID) "
sSql7 = sSql7 & " join Designation On (Grade.DesignationID= Designation.DesignationID) "
sSql7 = sSql7 & " WHERE Employee.ClientEmpID Like '%" & ClientEmpIDx & "%'"
cmd.CommandText = sSql7
da7.SelectCommand = cmd
da7.Fill(dt17)
End Sub
解决方案
Select PromotionLog.EmpID as [Emp ID],
Employee.ClientEmpID as [EMP No],
RTRIM(Employee.Initials) + LTRIM(Employee.LastName) As [Name],
PromotionLog.OldGradeID,
RTRIM(G.GradeName)
,
PromotionLog.NewGradeID as [New Grade ID],
RTRIM(Designation.DesName) as [New Desig],
PromotionLog.PromotedDate as [Promoted Date],
PromotionLog.SystemPromotedDate as [System Promoted Date],
RTRIM(PromotionLog.Reason) as [Remarks]
FROM Employee j
Join PromotionLog on (Employee.EmpID= PromotionLog.EmpID)
join Grade on (PromotionLog.NewGradeID= Grade.GradeID)
LEFT JOIN Grade G ON G.GradeID = PROMOTIONLOG.OLDGradeID
join Designation On (Grade.DesignationID= Designation.DesignationID)
WHERE Employee.ClientEmpID Like '%" & ClientEmpIDx & "%'
推荐阅读
- sql-server - 在 Excel Microsoft Data Query 中嵌入用户名和密码,以便其他用户可以刷新数据
- java - 找不到答案,Jersey 404 错误,当转到网页 rest/*,git 代码 https://gitlab.com/Kaidstor/job-access
- python - OpenCV 只接受我在 PyCharm 中的路径字符串
- c# - 使用 CryptoStream 将位图转换为 base 64 字符串格式
- reactjs - 无法将参数传递给功能组件之间的路由
- bar-chart - 按维度计数的前 N 个 Tableau
- android - 在 AndroidManifest 中使用 requestLegacyExternalStorage = true 在 Android API 29 和 30 上删除文件时获取 RecoverableSecurityException
- angular - 如何在没有互联网连接的情况下使用传单地图(离线模式),最好在 Angular 中使用
- r - stat_compare_means 比较不在 x 轴上的组
- swift - 可变 3 列 NavigationView SwiftUI