错误消息“无法绑定多部件标识符”
错误消息“无法绑定多部件标识符”
提问于 2017-01-11 10:28:24
我的代码是：
,CASE WHEN ResidenceInd = 1 THEN 'Residence' ELSE 'Day' END AS ClassType
,sc.Score
,'1' AS IsLP
,IncludeInGraph
,LessonOrder
FROM (
SELECT
sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
,DHDR.LessonOrder
FROM StdtSessColScore sc
INNER JOIN DSTempSetColCalc dcal
ON dcal.DSTempSetColCalcId = sc.DSTempSetColCalcId
INNER JOIN StdtSessionHdr hdr
ON hdr.StdtSessionHdrId=sc.StdtSessionHdrId
INNER JOIN Class Cls
ON Cls.ClassId=hdr.StdtClassId
INNER JOIN DSTempHdr DHDR
ON DHDR.DSTempHdrId=hdr.DSTempHdrId
WHERE
hdr.IOAInd='N'
AND hdr.SessMissTrailStus ='N'
AND hdr.SessionStatusCd='S'
AND (SELECT StatusId
FROM DSTempHdr DS
WHERE DS.DSTempHdrId = hdr.DSTempHdrId)
<>
(SELECT LookupId
FROM LookUp
WHERE LookupType = 'TemplateStatus'
AND LookupName='Deleted')
GROUP BY
DHDR.LessonOrder
,sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
) AS StdCalcs
,ReportPeriod
WHERE
StdCalcs.StudentId=1240
AND CONVERT(DATE,'2016/01/02') <= CONVERT(DATE,ReportPeriod.PeriodDate)
AND CONVERT(DATE,ReportPeriod.PeriodDate) <= CONVERT(DATE,'2016/12/28')
AND sc.SchoolId = StdCalcs.SchoolId
AND sc.StudentId = StdCalcs.StudentId
AND sc.DSTempSetColCalcId = StdCalcs.DSTempSetColCalcId
AND CONVERT(DATE, Hdr.EndTs) = CONVERT(DATE, ReportPeriod.PeriodDate)
AND Hdr.LessonPlanId = StdCalcs.LessonPlanId
AND dcal.CalcType = StdCalcs.CalcType
AND Hdr.StdtClassId = StdCalcs.StdtClassId
AND Hdr.IOAInd = 'N' AND Hdr.SessMissTrailStus ='N'
AND Hdr.SessionStatusCd = 'S'
AND sc.Score >= 0
AND Hdr.IsMaintanace = 0
GROUP BY 密码继续..。
我收到了一个错误：
多部分标识符sc.SchoolId、sc.StudentId、sc.DSTempSetColCalcId、Hdr.EndTs、Hdr.LessonPlanId、dcal.CalcType、Hdr.StdtClassId、Hdr.IOAInd、Hdr.SessMissTrailStus、Hdr.SessionStatusCd、sc.ScoreHdr.IsMaintanace无法绑定。
实际上我们怎么解决它..。我试着添加sc.score和
AND sc.SchoolId = StdCalcs.SchoolId
AND sc.StudentId = StdCalcs.StudentId
AND sc.DSTempSetColCalcId = StdCalcs.DSTempSetColCalcId
AND CONVERT(DATE, Hdr.EndTs) = CONVERT(DATE, ReportPeriod.PeriodDate)
AND Hdr.LessonPlanId=StdCalcs.LessonPlanId
AND dcal.CalcType = StdCalcs.CalcType
AND Hdr.StdtClassId = StdCalcs.StdtClassId
AND Hdr.IOAInd = 'N' AND Hdr.SessMissTrailStus = 'N'
AND Hdr.SessionStatusCd = 'S'
AND sc.Score >= 0
AND Hdr.IsMaintanace=0任何帮助都是非常感谢的。
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-01-11 10:50:54
检查下面代码中的注释。如果以表的形式从子查询中获取数据，则不能使用子查询的内部表别名，而必须使用给它的别名作为表。
CASE WHEN ResidenceInd=1 THEN 'Residence' ELSE 'Day' END AS ClassType
,StdCalcs.Score --here was the wrong table-alias
,'1' AS IsLP
,IncludeInGraph
,LessonOrder
FROM (
SELECT
sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
,DHDR.LessonOrder
FROM StdtSessColScore sc
INNER JOIN DSTempSetColCalc dcal
ON dcal.DSTempSetColCalcId = sc.DSTempSetColCalcId
INNER JOIN StdtSessionHdr hdr
ON hdr.StdtSessionHdrId=sc.StdtSessionHdrId
INNER JOIN Class Cls ON Cls.ClassId=hdr.StdtClassId
INNER JOIN DSTempHdr DHDR ON DHDR.DSTempHdrId=hdr.DSTempHdrId
WHERE hdr.IOAInd='N' AND hdr.SessMissTrailStus ='N' AND hdr.SessionStatusCd='S' AND (SELECT StatusId FROM DSTempHdr
DS WHERE DS.DSTempHdrId=hdr.DSTempHdrId)<>(SELECT LookupId FROM LookUp WHERE LookupType='TemplateStatus' AND LookupName='Deleted')
GROUP BY
DHDR.LessonOrder
,sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
) AS StdCalcs
,ReportPeriod
WHERE StdCalcs.StudentId=1240 AND CONVERT(DATE,'2016/01/02') <= CONVERT(DATE,ReportPeriod.PeriodDate) AND
CONVERT(DATE,ReportPeriod.PeriodDate) <= CONVERT(DATE,'2016/12/28') and sc.SchoolId=StdCalcs.SchoolId AND sc.StudentId=StdCalcs.StudentId
AND sc.DSTempSetColCalcId=StdCalcs.DSTempSetColCalcId AND CONVERT(DATE,Hdr.EndTs)=CONVERT(DATE,ReportPeriod.PeriodDate)
AND Hdr.LessonPlanId=StdCalcs.LessonPlanId AND dcal.CalcType=StdCalcs.CalcType AND Hdr.StdtClassId=StdCalcs.StdtClassId
AND Hdr.IOAInd='N' AND Hdr.SessMissTrailStus ='N' AND Hdr.SessionStatusCd='S' AND sc.Score>=0 AND Hdr.IsMaintanace=0
GROUP BY Stack Overflow用户
发布于 2017-01-11 11:09:52
--这不是一个答案！--这只是关于如何构造查询以更好地理解它的建议。我建议您花更多的时间在如何美化您的代码。今后，您将在调试上花费更少的时间，并了解您的代码正在做什么。这是您的代码：
SELECT CASE
WHEN ResidenceInd = 1 THEN 'Residence'
ELSE 'Day'
END AS ClassType
,StdCalcs.Score --here was the wrong table-alias
,'1' AS IsLP
,IncludeInGraph
,LessonOrder
FROM
(
SELECT
sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
,DHDR.LessonOrder
FROM StdtSessColScore AS sc INNER JOIN
DSTempSetColCalc AS dcal ON dcal.DSTempSetColCalcId = sc.DSTempSetColCalcId INNER JOIN
StdtSessionHdr AS hdr ON hdr.StdtSessionHdrId = sc.StdtSessionHdrId INNER JOIN
Class AS Cls ON Cls.ClassId = hdr.StdtClassId INNER JOIN
DSTempHdr AS DHDR ON DHDR.DSTempHdrId = hdr.DSTempHdrId
WHERE hdr.IOAInd = 'N' AND
hdr.SessMissTrailStus = 'N' AND
hdr.SessionStatusCd = 'S' AND
(
SELECT StatusId
FROM DSTempHdr DS
WHERE DS.DSTempHdrId = hdr.DSTempHdrId
) <>
(
SELECT LookupId
FROM LookUp
WHERE LookupType = 'TemplateStatus' AND
LookupName = 'Deleted'
)
GROUP BY DHDR.LessonOrder
,sc.SchoolId
,sc.StudentId
,sc.DSTempSetColCalcId
,dcal.CalcType
,hdr.LessonPlanId
,hdr.StdtClassId
,Cls.ResidenceInd
,dcal.IncludeInGraph
) AS StdCalcs,
ReportPeriod
WHERE StdCalcs.StudentId = 1240 AND
CONVERT(DATE, '2016/01/02') <= CONVERT(DATE, ReportPeriod.PeriodDate) AND
CONVERT(DATE, ReportPeriod.PeriodDate) <= CONVERT(DATE, '2016/12/28') AND
sc.SchoolId = StdCalcs.SchoolId AND
sc.StudentId = StdCalcs.StudentId AND
sc.DSTempSetColCalcId=StdCalcs.DSTempSetColCalcId AND
CONVERT(DATE, Hdr.EndTs) = CONVERT(DATE, ReportPeriod.PeriodDate) AND
Hdr.LessonPlanId=StdCalcs.LessonPlanId AND
dcal.CalcType = StdCalcs.CalcType AND
Hdr.StdtClassId = StdCalcs.StdtClassId AND
Hdr.IOAInd = 'N' AND
Hdr.SessMissTrailStus = 'N' AND
Hdr.SessionStatusCd = 'S' AND
sc.Score >= 0 AND
Hdr.IsMaintanace = 0
GROUP BY 在select、group by、order 子句中:尝试将每一列放在它自己的行上。
在表单子句中:将每个表和联接中的on放在一行中。
在where子句中:将每个条件放在一行中。在开始或结束时放置和或或。
Everywhere：在等式、符号和后面写一个空格。您可以更容易地看到左边和右边的成员。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41588359
复制相关文章
相似问题
腾讯云开发者
