用exec(' + sql语句 + ')的形式,其中in判断条件全部写到参数中。
sql = @"exec('SELECT SWH.Date,SWH.Shift,SWH.FunctionUnit,SWH.ShiftAttendance,SWH.[ShiftTotalWorkingHours],SWH.OvertimeAttendance,SWH.[OvertimeTotalWorkingHours],
SWH.[TotalShiftWorkingHours],WH.[TotalActualWorkingHours]
FROM (SELECT Date,Shift,FunctionUnit,SUM(ShiftAttendance) ShiftAttendance,SUM(ShiftAttendance * ShiftWorkingHours) ShiftTotalWorkingHours,
SUM(OvertimeAttendance) OvertimeAttendance,SUM(OvertimeAttendance * OvertimeWorkingHours) OvertimeTotalWorkingHours,
SUM(ShiftAttendance * ShiftWorkingHours) + SUM(OvertimeAttendance * OvertimeWorkingHours) TotalShiftWorkingHours
FROM ProdLH_ShiftWorkingHours
WHERE '+@Shift+' and '+@FunctionUnit+' and Date>='+@DateStart+' and Date<'+@DateEnd+'
GROUP BY Date,Shift,FunctionUnit) SWH
LEFT JOIN
(SELECT Date,Shift,FunctionUnit,SUM(Hours) TotalActualWorkingHours
FROM ProdLH_WorkingHours
WHERE '+@Shift+' and '+@FunctionUnit+' and Date>='+@DateStart+' and Date<'+@DateEnd+'
GROUP BY Date,Shift,FunctionUnit) WH
ON SWH.Date=WH.Date AND SWH.Shift=WH.Shift AND SWH.FunctionUnit=WH.FunctionUnit')";
list = this.Get(sql, new { Shift = "Shift in (" + shift + ")", FunctionUnit = "FunctionUnit in (" + functionunit + ")",
DateStart = "'" + dateStart + "'", DateEnd = "'" + dateEnd + "'"}).ToList();
标签:SWH,C#,Shift,SUM,sqlserver,FunctionUnit,WH,Date,化传值 From: https://www.cnblogs.com/jxw-29/p/17133921.html