请问这句话有什么问题吗? [ 解决了 ] [英] whats wrong in this statement please ? [ SOLVED ]

问题描述

CREATE PROCEDURE dbo.StoredProcedure7
AS
SELECT *
FROM 
(SELECT dbo.Districts.name,count(distinct(dbo.Interview_Schedule.app_id)) AS nom,
            CASE  dbo.Interview_Schedule.interview_result
            WHEN NULL THEN ''no value''
            WHEN 0 THEN ''zero''
            WHEN 1 THEN ''one''
            END AS thevalue
 FROM Districts INNER JOIN
      Interview_Schedule ON Districts.id = Interview_Schedule.interview_district 
 WHERE dbo.Interview_Schedule.work_flow_id=1
 group by dbo.Districts.name)AS datatable
 PIVOT
( COUNT(thevalue)
  FOR thevalue IN ([no value],[zero],[one])) as piv

错误::

消息8120，级别16，状态1，过程StoredProcedure7，第3行
在选择列表中，"Interview_Schedule.interview_result"列无效，因为它既不在聚合函数中也不在GROUP BY子句中.
消息8120，级别16，状态1，过程StoredProcedure7，第3行

ERROR ::

Msg 8120, Level 16, State 1, Procedure StoredProcedure7, Line 3
Column ''Interview_Schedule.interview_result'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure StoredProcedure7, Line 3
Column ''Interview_Schedule.interview_result'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

推荐答案

很常见的错误.

请参阅 http://social.msdn.microsoft.com/Forums /en/transactsql/thread/dea07916-d7d0-4482-a906-24fd8d567edc [

Very common error.

See http://social.msdn.microsoft.com/Forums/en/transactsql/thread/dea07916-d7d0-4482-a906-24fd8d567edc[^]

从您的查询中删除dbo.Districts.name，它应该可以正常工作.

Remove dbo.Districts.name from your query and it should work fine.

select D.[Name] as [District Name], sum(case when I.Interview_Result = 1 then 1 end) as [Count of 1],
sum(case when I.Interview_Result = 0 then 1 end) as [Count of 0],
sum(case when I.Interview_Result IS NULL then 1 end) as [Count of NULLs]
from Districts D inner join Interview_Schedule I on D.ID = I.Interview_District
GROUP BY D.[ID],D.[Name]

(已复制)


我找到了..

(copied)


i found it ..

这篇关于请问这句话有什么问题吗? [ 解决了 ]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！