看看一个面试题（设计学生、课程、选修课和相关SQL）SQL SERVER环境

[garfieldzf8]

好长时间没写sql语句了， 昨儿拿到这个sql，一时没什么思路，大家一起来看看。

[Cherise_huang]

首先创建三个基础表：学生表(Students),课程表（Subjects），教室表（ClassRoom） 在创建一个关系表;选课表（elective）

--创建学生表
CREATE TABLE Students
(
 id INT,
 NAME VARCHAR(20),
 sex  VARCHAR(20),
 Age  VARCHAR(20)	
)
--创建课程表
CREATE TABLE Subjects
(
 id INT,
 SNAME VARCHAR(20),
 Stime  datetime	
)
--创建教室表
CREATE TABLE ClassRoom
(
 id INT,
 CNAME VARCHAR(20),
 TeacherName  VARCHAR(20)	
)

--创建关系表选课表
CREATE TABLE Elective
(
 id INT,
 NAME VARCHAR(20),
 Subjects  VARCHAR(20),
 TeacherName  VARCHAR(20),
 Etime datetime	
)

[Cherise_huang]

上面的修改了一部分： 如下：

--创建学生表
CREATE TABLE Students
(
 StudentId VARCHAR(20),
 NAME VARCHAR(20),
 sex  CHAR(20),
 Age  int	
)
--创建课程表
CREATE TABLE Subjects
(
 id INT,
 SNAME VARCHAR(20),
 Stime  datetime	
)
--创建教室表
CREATE TABLE ClassRoom
(
 id INT,
 CNAME VARCHAR(20),
 TeacherName  VARCHAR(20)	
)

--创建关系表选课表
CREATE TABLE Elective
(
 id INT,
 Studentid VARCHAR(20),
 StudentName VARCHAR(20),
 Subjects  VARCHAR(20),
 TeacherName  VARCHAR(20),
 Etime datetime	
)
--创建签到表
CREATE TABLE Checks
(
 id INT,
 StudentID VARCHAR(20),
 SNAME VARCHAR(20),
 CHTime datetime	
)

INSERT INTO Students
SELECT '001','丁一宇','男','19' union all
SELECT '002','伊恩惠','女','20'  


INSERT INTO Elective
SELECT '1','001','丁一宇','高等数学','刘佳丽','2015-04-25' union all
SELECT '2','002','伊恩惠','语文','刘佳丽','2015-02-14' union all
SELECT '3','003','娄挺','高等数学','刘佳丽','2015-04-25' union all
SELECT '4','001','丁一宇','舞蹈鉴赏','刘佳丽','2015-04-27' union all
SELECT '5','001','丁一宇','音乐鉴赏','孟静娴','2015-04-24' union all
SELECT '6','004','惠子','高等数学','王子涵','2015-04-25' union all
SELECT '7','003','娄挺','高等数学','刘佳丽','2015-04-25' union all
SELECT '8','001','丁一宇','体育','沈美男','2015-02-14' union all
SELECT '9','001','丁一宇','绘画','张琳琳','2015-04-22' 

--查看那个学生选课最多
SELECT * FROM
            (
			SELECT 
			     ROW_NUMBER() over(order by count(StudentName) desc) as 根据选课总数排序,
				 StudentName AS 姓名,
				 count(StudentName) AS 选课总数 
			FROM Elective GROUP BY StudentName 
            )a WHERE 根据选课总数排序=1
            
--查看情人节那天男生和女生的选课人数          
SELECT COUNT(name) OVER(Partition BY s1.sex) as 情人节那天选课人数,
      s1.sex
FROM Elective E1 
inner join Students s1 
ON E1.Studentid=s1.StudentId  
WHERE  e1.Etime='2015-02-14' 

[Landa_Tiny_cui]

恭喜二楼 面试通过！

[xiaoxiangqing]

//楼上的有问题，就是重复的情况下，只会选择一笔。只要把row_number改为rank就可以了 if object_id('Students')>0 DROP TABLE Students if object_id('Subjects')>0 DROP TABLE Subjects if object_id('ClassRoom')>0 DROP TABLE ClassRoom if object_id('Elective')>0 DROP TABLE Elective if object_id('Checks')>0 DROP TABLE Checks go --创建学生表 CREATE TABLE Students ( StudentId VARCHAR(20), NAME VARCHAR(20), sex CHAR(20), Age int ) --创建课程表 CREATE TABLE Subjects ( id INT, SNAME VARCHAR(20), Stime datetime ) --创建教室表 CREATE TABLE ClassRoom ( id INT, CNAME VARCHAR(20), TeacherName VARCHAR(20) ) --创建关系表选课表 CREATE TABLE Elective ( id INT, Studentid VARCHAR(20), StudentName VARCHAR(20), Subjects VARCHAR(20), TeacherName VARCHAR(20), Etime datetime ) --创建签到表 CREATE TABLE Checks ( id INT, StudentID VARCHAR(20), SNAME VARCHAR(20), CHTime datetime ) INSERT INTO Students SELECT '001','丁一宇','男','19' union all SELECT '002','伊恩惠','女','20' INSERT INTO Elective SELECT '1','001','丁一宇','高等数学','刘佳丽','2015-04-25' union all SELECT '2','002','伊恩惠','语文','刘佳丽','2015-02-14' union all SELECT '3','003','娄挺','高等数学','刘佳丽','2015-04-25' union all SELECT '4','001','丁一宇','舞蹈鉴赏','刘佳丽','2015-04-27' union all SELECT '5','001','丁一宇','音乐鉴赏','孟静娴','2015-04-24' union all SELECT '6','004','惠子','高等数学','王子涵','2015-04-25' union all SELECT '7','003','娄挺','高等数学','刘佳丽','2015-04-25' union all SELECT '8','001','丁一宇','体育','沈美男','2015-02-14' union all SELECT '9','001','丁一宇','绘画','张琳琳','2015-04-22' union all SELECT '4','001','惠子','舞蹈鉴赏','刘佳丽','2015-04-27' union all SELECT '5','001','惠子','音乐鉴赏','孟静娴','2015-04-24' union all SELECT '8','001','惠子','体育','沈美男','2015-02-14' union all SELECT '9','001','惠子','绘画','张琳琳','2015-04-22' --查看那个学生选课最多 SELECT * FROM ( SELECT RANK() over(order by count(StudentName) desc) as 根据选课总数排序, StudentName AS 姓名, count(StudentName) AS 选课总数 FROM Elective GROUP BY StudentName )a WHERE 根据选课总数排序=1

[gw6328]

学生表
课程表 +[上课时间表]
选课表
+[点名表]

1.select top 1 studentId from 选课表 group by studentid order by count(1) desc
2.从这个题目来看，这里的课程表会复杂一下，不仅仅是课程名，还有个上课时间，所以这里会多出一个表来就是时间，里面存的是 课程号，老师，上课时间。所以现在就是要这个表里面选择 情人节那天的程课，并通过选课表找到学生，
3.从这个问题来看，还有一个上课的点名记录表，里面有上课时间表的id，上课学生id; 这个表和选课表配置出结果，这里出题人应该给个时间等条件，说明哪天，或者一段时间没有去上过课，或者存在旷课的记录的学生
4.通过上课时间表与点名表配合出结果