create procedure Pro_QCname_Chour_xkrs_AverScore_MaxScore @Tname_in char(20),@Cname_in char(30), @Chour_out tinyint output,@xkrs_out int output, @Aver_Score tinyint output,@Max_Score tinyint output as declare @Sum_Score int select @Chour_out=Chour,@xkrs_out=count(Sno),@Sum_Score=sum(Score),@Max_Score=max(Score) from SC,Course,Teacher,TC where Course.Cno=SC.Cno and Teacher.Tno=TC.Tno and Course.Cno=TC.Cno group by SC.Cno having Tname=@Tname_in and Cname=@Cname_in select @Aver_Score=@Sum_Score/@xkrs_out go，选择列表中的列 'Course.Chour' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

这个错误是因为在查询语句中没有将所有非聚合列包含在 GROUP BY 子句中。在这个例子中，Course.Chour 列没有被包含在 GROUP BY 子句中，所以会报错。你需要在查询语句中将所有非聚合列包含在 GROUP BY 子句中，例如：

select Course.Chour, count(SC.Sno) as xkrs, avg(SC.Score) as Aver_Score, max(SC.Score) as Max_Score
from SC,Course,Teacher,TC
where Course.Cno=SC.Cno and Teacher.Tno=TC.Tno and Course.Cno=TC.Cno
group by Course.Chour, Course.Cname, Teacher.Tname
having Teacher.Tname=@Tname_in and Course.Cname=@Cname_in

这个查询语句将 Course.Chour 列包含在了 GROUP BY 子句中，同时也包含了其他需要聚合的列。

相关问题

SELECT a.username, a.userid, a.orgname, a.orgnamehr orgnameHr, a.company company, a.idcard, sum(CASE a.hour_type WHEN 'A' THEN a.allot_hour ELSE 0 END ) aHour, sum(CASE a.hour_type WHEN 'B' THEN a.allot_hour ELSE 0 END ) bHour, sum(CASE a.hour_type WHEN 'C' THEN a.allot_hour ELSE 0 END ) cHour, sum(CASE a.hour_type WHEN 'D' THEN a.allot_hour ELSE 0 END ) dHour, sum(CASE a.hour_type WHEN 'E' THEN a.allot_hour ELSE 0 END ) eHour, sum(CASE a.hour_type WHEN 'F' THEN a.allot_hour ELSE 0 END ) fHour, sum(CASE WHEN a.hour_type IN ('A','B','C','D','E','F') THEN a.allot_hour ELSE 0 END ) countHour, CASE yearMonth WHEN CONVERT(varchar(10), d.break_sure_date, 23) >= CONVERT(varchar(10),'2023-06-06', 23) and CONVERT(varchar(10), d.break_sure_date, 23) < CONVERT(varchar(10),'2023-07-06', 23) THEN '1' ELSE '2' END FROM ems_hour_allot a 这个sql语法有错误吗

根据提供的SQL查询，有一个语法错误。在查询的末尾缺少一个`GROUP BY`子句，以指定如何对结果进行分组。根据查询中的列，你可能需要在`GROUP BY`子句中包含以下列：`a.username`、`a.userid`、`a.orgname`、`a.orgnamehr`、`a.company`、`a.idcard`和`yearMonth`。

以下是修正后的查询：

SELECT
	a.username,
	a.userid,
	a.orgname,
	a.orgnamehr AS orgnameHr,
	a.company,
	a.idcard,
	SUM(CASE a.hour_type WHEN 'A' THEN a.allot_hour ELSE 0 END) AS aHour,
	SUM(CASE a.hour_type WHEN 'B' THEN a.allot_hour ELSE 0 END) AS bHour,
	SUM(CASE a.hour_type WHEN 'C' THEN a.allot_hour ELSE 0 END) AS cHour,
	SUM(CASE a.hour_type WHEN 'D' THEN a.allot_hour ELSE 0 END) AS dHour,
	SUM(CASE a.hour_type WHEN 'E' THEN a.allot_hour ELSE 0 END) AS eHour,
	SUM(CASE a.hour_type WHEN 'F' THEN a.allot_hour ELSE 0 END) AS fHour,
	SUM(CASE WHEN a.hour_type IN ('A','B','C','D','E','F') THEN a.allot_hour ELSE 0 END) AS countHour,
	CASE
		WHEN CONVERT(varchar(10), d.break_sure_date, 23) >= CONVERT(varchar(10),'2023-06-06', 23) AND CONVERT(varchar(10), d.break_sure_date, 23) < CONVERT(varchar(10),'2023-07-06', 23) THEN '1'
		ELSE '2'
	END AS yearMonth
FROM
	ems_hour_allot a
GROUP BY
	a.username,
	a.userid,
	a.orgname,
	a.orgnamehr,
	a.company,
	a.idcard,
	yearMonth;

请注意，此修正假设`ems_hour_allot`表中的列`d.break_sure_date`存在并且可以在查询中使用。如果该列不存在或者需要使用其他列，请相应地进行修改。