UC知道SQL查询问题,急!!!
来源：百度知道 编辑：UC知道 时间：2024/06/06 01:01:22
查询第一学年各科成绩及格的人数和没有及格的人数，要求显示: 科目、及格人数、不及格人数，按"及格人数"降序排列
要求一条SQL语句完成.
----------------------------------------------------------------
CREATE DATABASE student0803
---------------------------------------------------------------
GO
USE student0803
GO
CREATE TABLE [dbo].[RESULTS] ([STUDENT_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[COURSE_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[COURSE_SCORE] [float] NULL ,
[SEMESTER_TYPE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[STUDENTS] (
[STUDENT_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STUDENT_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STUDENT_CLASS] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SEMESTER_TYPE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]
GO
ALTER TABLE [dbo].[STUDENTS] ADD
CONSTRAINT [PK_STUDENTS] PRIMARY KEY
要求一条SQL语句完成.
----------------------------------------------------------------
CREATE DATABASE student0803
---------------------------------------------------------------
GO
USE student0803
GO
CREATE TABLE [dbo].[RESULTS] ([STUDENT_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[COURSE_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[COURSE_SCORE] [float] NULL ,
[SEMESTER_TYPE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[STUDENTS] (
[STUDENT_NO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STUDENT_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STUDENT_CLASS] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SEMESTER_TYPE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]
GO
ALTER TABLE [dbo].[STUDENTS] ADD
CONSTRAINT [PK_STUDENTS] PRIMARY KEY
select COURSE_NAME,count(1) as 及格人数,(select count(1) from RESULTS as a
where a.SEMESTER_TYPE='第一学年' and a.COURSE_SCORE<60 and a.COURSE_NAME=b.COURSE_NAME) as 不及格人数 from
RESULTS b where SEMESTER_TYPE='第一学年' and COURSE_SCORE>=60 group by COURSE_NAME
sql下测试通过