SELECT c.kcmc, c.KCH FROM JW_CJ_XSCJB c JOIN (select * from JW_CJ_XSCJB where xnm = "2022" and xqm = '12') s ON c.KCH = s.KCH GROUP BY c.kch
时间: 2024-04-15 15:26:15 浏览: 144
你的查询语句是正确的,它使用了子查询来筛选出特定学年("2022")和学期("12")的数据,并通过 JOIN 操作将结果与主查询中的表进行关联。然后,使用 GROUP BY 子句按照课程号(KCH)进行分组,并选择课程名称(kcmc)和课程号(KCH)进行显示。
请注意,你的查询中使用了表别名 "c" 和 "s",这是一个很好的做法,可以提高查询语句的可读性和可维护性。
如果你有任何进一步的问题或需要更多帮助,请随时告诉我。
相关问题
SELECT DISTINCT XSXXB.xm as xsxm, XSXXB.XH as xszh, XSXXB.yxmc as YXMC, XSXXB.ZYMC as ZYMC, XSXKXXB.KCMC as KCMC, XSXKXXB.kclb as KCLB, XSXXB.bjmc as bjmc, XSXKXXB.SJCJSJ as xsxksh, 30 as xsrs, XGSJ_class_schedule_SJZX2.TEACHER_NAME AS JSXM, jsxxb.jzgh as JSZH, SUBSTR(semester, 0, 9) as xn, CASE SUBSTR(semester, 11, 1) WHEN '1' THEN '上学期' ELSE '下学期' END XQ, XGSJ_class_schedule_SJZX2.Day AS jsbgsj -- , -- row_number() over (partition by COURSE_NAME order by COURSE_NAME ASC) rn FROM XSXXB LEFT JOIN -- 学生选课信息表 XSXKXXB ON XSXXB.xh = XSXKXXB.xh -- 学生课程表 LEFT JOIN XGSJ_class_schedule_SJZX2 ON XSXKXXB.xh =XGSJ_class_schedule_SJZX2.STUDENT_CODE AND xsxkxxb.KCMC =XGSJ_class_schedule_SJZX2.course_name -- 教师信息表 LEFT JOIN jsxxb ON jsxxb.JZGXM =XGSJ_class_schedule_SJZX2.TEACHER_NAME WHERE XSXXB.bjmc LIKE '22%' AND XSXXB.xjzt ='在校' AND XSXXB.yxmc ='数字创意与动画学院' AND XGSJ_class_schedule_SJZX2.semester like '2022-2023%' AND JSXXB.XYMC ='数字创意与动画学院' -- AND XSXXB.Xm ='廖远君' ) GROUP BY "XSXM","XSZH","YXMC","ZYMC","KCMC","KCLB","BJMC","XSXKSH","XSRS","JSXM","JSZH","XN","XQ" > ORA-00933: SQL command not properly ended
这是一条SQL语句,它的意思是从XSXXB、XSXKXXB、XGSJ_class_schedule_SJZX2、jsxxb几张表中获取数据,并通过LEFT JOIN关联起来。
它的作用是查询学生信息表、学生选课信息表、学生课程表、教师信息表中符合条件的数据,然后按照指定的格式返回结果。具体条件包括:
- XSXXB.bjmc LIKE 22%:学生信息表中班级名称以22开头。
- XSXXB.xjzt =u5728u6821:学生信息表中在校状态为在校。
- XSXXB.yxmc =u6570u5b57u521bu610fu4e0eu52a8u753bu5b66u9662:学生信息表中院系名称为数字媒体学院。
- XGSJ_class_schedule_SJZX2.semester like 2022-2023%:学生课程表中学期以2022-2023开头。
- JSXXB.XYMC =u6570u5b57u521bu610fu4e0eu52a8u753bu5b66u9662:教师信息表中学院名称为数字媒体学院。
最后,结果会按照"XSXM","XSZH","YXMC","ZYMC","KCMC","KCLB","BJMC","XSXKSH","XSRS","JSXM","JSZH","XN","XQ"分组并返回。
但是,这条SQL语句有一个语法错误,会导致错误代码ORA-00933:SQL命令未正确结束。错误的原因可能是在SQL语句的最后多了一个符号">"。
修改sql语句的错误: create function getid(@xh char(10)) returns @output_table table(@XM varchar(10), @XB varchar(10), @KCH int, @MS varchar(10), @XF int) as begin declare @xm varchar(10),@xb varchar(10),@kch int = 0, @ms varchar(10) = '', @xf int = 0 declare @kcmc varchar(10) , @cj int, @xh char(10) declare cur_score cursor for select 课程表1 .课程名称, 成绩表.期末成绩 from 课程表1 JOIN 成绩表 on 成绩表.课程号 = 课程表1 .课程号 where 成绩表.学号 = @xh open cur_score fetch next from cur_score into @kcmc, @cj while @@FETCH_STATUS = 0 begin if @cj >= 60 begin set @xf = @xf +课程表1.学分 end set @ms = @ms + @kcmc + ',' set @kch = @kch + 1 fetch next from cur_score into @kcmc, @cj end close cur_score deallocate cur_score select @xm = 姓名, @xb = 性别 from 学生表 where 学号 = @xh insert into @output_table values(@xm, @xb, @kch, left(@kcmc, len(@ms) - 1), @xf) return end
根据提供的 SQL 语句,我看到一个问题:在函数声明中,返回表的列名应该使用不带@符号的名称,因为这些名称将在查询表达式中使用。以下是修改后的代码:
```
create function getid(@xh char(10))
returns @output_table table(XM varchar(10), XB varchar(10), KCH int, MS varchar(10), XF int)
as
begin
declare @xm varchar(10),@xb varchar(10),@kch int = 0, @ms varchar(10) = '', @xf int = 0
declare @kcmc varchar(10) , @cj int, @xh char(10)
declare cur_score cursor for
select 课程表1.课程名称, 成绩表.期末成绩
from 课程表1
JOIN 成绩表 on 成绩表.课程号 = 课程表1.课程号
where 成绩表.学号 = @xh
open cur_score
fetch next from cur_score into @kcmc, @cj
while @@FETCH_STATUS = 0
begin
if @cj >= 60
begin
set @xf = @xf + 课程表1.学分
end
set @ms = @ms + @kcmc + ','
set @kch = @kch + 1
fetch next from cur_score into @kcmc, @cj
end
close cur_score
deallocate cur_score
select @xm = 姓名, @xb = 性别 from 学生表 where 学号 = @xh
insert into @output_table values(@xm, @xb, @kch, left(@ms, len(@ms) - 1), @xf)
return
end
```
请注意,在返回表声明中,我将 @XM 和 @XF 列名更改为 XM 和 XF,以便它们可以在查询表达式中使用。