z-notes

SQL SERVER-设计学生成绩的汇总表

一、题目

要求：用SQL语句实现上图的表格

二、解题

2.1.创建数据库

CREATE DATABASE [SCORE] ON PRIMARY(
    NAME = 'SCORE_DATA',
    FILENAME = 'D:\SQL-TEST\SCORE_DATA.mdf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 5%
)
LOG ON 
(
    NAME = 'SCORE_LOF',
    FILENAME = 'D:\SQL-TEST\SCORE_LOF.ldf',
    SIZE = 2MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 5%
)

2.2.创建表格，插入数据

CREATE TABLE SC3(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    YUWEN INT,
    SHUXUE INT,
    YINGYU INT
)
INSERT INTO SC3 VALUES(1,'张三',60,70,80),(2,'李四',80,90,50);
INSERT INTO SC3 (ID,NAME,YINGYU) VALUES(3,'王五',75);

2.3.创建查询语句

--方法一：
SELECT NAME AS 姓名,YUWEN AS 语文,SHUXUE AS 数学,YINGYU AS 英语,总分,平均分,排名 FROM (

SELECT ID,NAME,YUWEN,SHUXUE,YINGYU,COALESCE(YUWEN,0)+COALESCE(SHUXUE,0)+COALESCE(YINGYU,0) AS 总分,CAST((COALESCE(YUWEN,0)+COALESCE(SHUXUE,0)+COALESCE(YINGYU,0))/3 as DECIMAL(10,2)) AS 平均分,RANK() OVER (ORDER BY (COALESCE(YUWEN,0)+COALESCE(SHUXUE,0)+COALESCE(YINGYU,0)) desc) as 排名 FROM SC4

) AS T1 ORDER BY ID;

--方法二：
-- T2
SELECT 姓名,语文,数学,英语,总分,平均分,排名 FROM (
-- T1
SELECT ID,NAME AS 姓名,YUWEN AS 语文,SHUXUE AS 数学,YINGYU AS 英语,总分,平均分,RANK() OVER (ORDER BY 总分 DESC) AS 排名 FROM (
-- S4
SELECT ID,NAME,YUWEN,SHUXUE,YINGYU,COALESCE(YUWEN,0)+COALESCE(SHUXUE,0)+COALESCE(YINGYU,0) AS 总分,cast((COALESCE(YUWEN, 0) + COALESCE(SHUXUE, 0) + COALESCE(YINGYU, 0)) / 3 as decimal(10,2)) AS 平均分 FROM SC4) 
AS T1) 
AS T2 ORDER BY ID;