sql查询语句相关问题

Question

“如果某专业尚未由任何学生选修课程或成绩为空，平均分记为0”，这个要求要怎么实现呢？

-- 建表语句
CREATE TABLE major (
mno char(2) NOT NULL,
mname nvarchar(20) NOT NULL,
PRIMARY KEY (mno)
);
-- 学生表
CREATE TABLE stu (
sno char(4) NOT NULL,
sname nvarchar(8) NOT NULL,
sex smallint DEFAULT NULL,
mno char(2) DEFAULT NULL,
birdate datetime DEFAULT NULL,
memo ntext,
PRIMARY KEY (sno),
CONSTRAINT fk_stu_mno FOREIGN KEY (mno) REFERENCES major (mno)
);
-- 课程表
CREATE TABLE cou (
cno char(4) NOT NULL,
cname nvarchar(30) NOT NULL,
credit smallint DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(4,1) DEFAULT NULL,
PRIMARY KEY ( sno , cno ),
CONSTRAINT fk_sc_cno FOREIGN KEY ( cno ) REFERENCES cou ( cno ),
CONSTRAINT fk_sc_sno FOREIGN KEY ( sno ) REFERENCES stu ( sno )
);

--创建数据库+数据

-- MySQL dump 10.13 Distrib 5.7.12, for Win32 (AMD64)

-- Host: localhost Database: marks

---

-- Server version 5.6.35-log

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

--

-- Table structure for table cou

DROP TABLE IF EXISTS cou;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE cou (
cno char(4) NOT NULL,
cname varchar(30) NOT NULL,
credit smallint(6) DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher varchar(10) DEFAULT NULL,
PRIMARY KEY (cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;

--

-- Dumping data for table cou

LOCK TABLES cou WRITE;
/!40000 ALTER TABLE cou DISABLE KEYS /;
INSERT INTO cou VALUES ('C001','1',1,'1','1'),('C002','1',1,'1','1'),('C003','1',1,'1','1'),('C004','1',1,'1','1'),('C005','1',1,'1','1'),('C006','1',1,'1','1');
/!40000 ALTER TABLE cou ENABLE KEYS /;
UNLOCK TABLES;

--

-- Table structure for table major

DROP TABLE IF EXISTS major;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE major (
mno char(2) NOT NULL,
mname varchar(20) NOT NULL,
PRIMARY KEY (mno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;

--

-- Dumping data for table major

LOCK TABLES major WRITE;
/!40000 ALTER TABLE major DISABLE KEYS /;
INSERT INTO major VALUES ('01','计算机工程'),('02','软件工程'),('03','网络工程'),('04','智能技术');
/!40000 ALTER TABLE major ENABLE KEYS /;
UNLOCK TABLES;

--

-- Table structure for table sc

DROP TABLE IF EXISTS sc;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(4,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
KEY fk_sc_cno (cno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES cou (cno),
CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES stu (sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;

--

-- Dumping data for table sc

LOCK TABLES sc WRITE;
/!40000 ALTER TABLE sc DISABLE KEYS /;
INSERT INTO sc VALUES ('S001','C001',72.0),('S001','C002',95.0),('S001','C003',50.0),('S002','C001',75.0),('S003','C001',93.0),('S003','C005',78.0),('S003','C006',67.0),('S004','C002',67.0),('S004','C004',50.0),('S005','C002',80.0),('S010','C001',NULL);
/!40000 ALTER TABLE sc ENABLE KEYS /;
UNLOCK TABLES;

--

-- Table structure for table stu

DROP TABLE IF EXISTS stu;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE stu (
sno char(4) NOT NULL,
sname varchar(8) NOT NULL,
sex smallint(6) DEFAULT NULL,
mno char(2) DEFAULT NULL,
birdate datetime DEFAULT NULL,
memo text,
PRIMARY KEY (sno),
KEY fk_stu_mno (mno),
CONSTRAINT fk_stu_mno FOREIGN KEY (mno) REFERENCES major (mno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;

--

-- Dumping data for table stu

LOCK TABLES stu WRITE;
/!40000 ALTER TABLE stu DISABLE KEYS /;
INSERT INTO stu VALUES ('S001','张三',1,'01','2001-10-01 00:00:01','二年级'),('S002','李四',1,'02','2001-10-01 00:00:01','1'),('S003','a',1,'01','2001-10-01 00:00:01','1'),('S004','b',1,'02','2001-10-01 00:00:01','1'),('S005','c',1,'02','2001-10-01 00:00:01','1'),('S006','d',1,'02','2001-10-01 00:00:01','1'),('S007','e',1,'01','2001-10-01 00:00:01','1'),('S008','f',1,'03','2001-10-01 00:00:01','1'),('S009','g',1,'03','2001-10-01 00:00:01','1'),('S010','h',1,'03','2001-10-01 00:00:01','1');
/!40000 ALTER TABLE stu ENABLE KEYS /;
UNLOCK TABLES;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE /;

/!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES /;

-- Dump completed on 2021-05-20 19:07:18

Answer 1

# 一条sql搞定
select c.cname as 专业, IFNULL(avg(sc.grade), 0) as 平均成绩 from cou as c
 left join sc on c.cno = sc.cno
group by c.cno

Answer 2

是我的话肯定是根据专业分组统计 关联学生关联分数 进行统计
统计结果不为数值的则给个0 这个直接if判断
至于有值的该是多少就是多少