我的查询有问题,我读了一些关于这方面的建议,但运气不好。
我有三张表,阶段,阶段和时间表。
计划列:
ID、阶段ID、阶段ID、持续时间、日程类型
阶段列:
阶段ID,阶段名称
相位列:
PhaseId,PhaseName
下面是我想要得到的:
DECLARE @actual_startdate date = '2014-01-10'
SELECT
DISTINCT (C.PhaseName),
A.Duration,
@actual_startdate StartDate,
DATEADD(dd, A.Duration, @actual_startdate) EndDate
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
ORDERBY B.StageId DESC;
我得到了这个错误:
如果指定了select DISTINCT,则ORDER BY项目必须出现在选择列表中
对于我的研究,我尝试了使用GROUP BY子句而不是DISTINCT的其他建议,但仍然没有成功。
我试过这个:
DECLARE @actual_startdate date = '2014-01-10'
SELECT
C.PhaseName,
A.Duration,
@actual_startdate StartDate,
DATEADD(dd, A.Duration, @actual_startdate) EndDate
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
GROUP BY C.PhaseName
order BY B.StageId DESC;
得到了新的错误:
"Schedule.Duration"列在选择列表中无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中
希望有人能帮我。谢谢!:)
您需要在Group By Clause 中添加A.Duration
DECLARE @actual_startdate date = '2014-01-10'
SELECT
C.PhaseName,
A.Duration,
@actual_startdate StartDate,
DATEADD(dd, A.Duration, @actual_startdate) EndDate
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
GROUP BY C.PhaseName,A.Duration,B.StageId
order BY B.StageId DESC;