废话不说，直接建表
1.表Player
1 USE T4st -- 设置当前数据库为T4st,以便访问sysobjects 2 IF EXISTS(SELECT * FROM sysobjects WHERE name='Player') 3 DROP TABLE Player 4 CREATE TABLE Player 5 ( 6 Code VARCHAR(4) PRIMARY KEY NOT NULL, 7 Name VARCHAR(50), 8 ) 9 INSERT INTO Player VALUES('0001','赵甲'); 10 INSERT INTO Player VALUES('0002','钱乙'); 11 INSERT INTO Player VALUES('0003','孙丙'); 12 INSERT INTO Player VALUES('0004','李丁'); 13 INSERT INTO Player VALUES('0005','周戊'); 14 INSERT INTO Player VALUES('0006','吴已'); 15 INSERT INTO Player VALUES('0007','郑庚'); 16 INSERT INTO Player VALUES('0008','王辛'); 17 INSERT INTO Player VALUES('0009','冯壬'); 18 INSERT INTO Player VALUES('0010','陈葵'); 19 20 SELECT * FROM Player 21 GO
2.表Record
1 USE T4st -- 设置当前数据库为T4st,以便访问sysobjects 2 IF EXISTS(SELECT * FROM sysobjects WHERE name='Record') 3 DROP TABLE Record 4 CREATE TABLE Record 5 ( 6 Time DATETIME PRIMARY KEY NOT NULL, 7 Code VARCHAR(4), 8 Height VARCHAR(5), 9 Sucess VARCHAR(5) 10 ) 11 INSERT INTO Record VALUES('2017-02-22 08:01:11','0001','1.80','False'); 12 INSERT INTO Record VALUES('2017-02-22 08:02:32','0005','1.71','True'); 13 INSERT INTO Record VALUES('2017-02-22 08:03:25','0001','1.81','True'); 14 INSERT INTO Record VALUES('2017-02-22 08:04:22','0008','1.90','False'); 15 INSERT INTO Record VALUES('2017-02-22 08:05:15','0008','1.90','True'); 16 INSERT INTO Record VALUES('2017-02-22 08:07:06','0001','1.90','False'); 17 INSERT INTO Record VALUES('2017-02-22 08:08:19','0003','1.91','True'); 18 INSERT INTO Record VALUES('2017-02-22 08:09:18','0002','1.92','True'); 19 INSERT INTO Record VALUES('2017-02-22 08:10:48','0005','1.81','True'); 20 INSERT INTO Record VALUES('2017-02-22 08:12:36','0010','1.79','False'); 21 22 SELECT * FROM Record 23 GO
3.判定特定时间段的成绩无效
1 USE T4st 2 DECLARE @time1 DATETIME,@time2 DATETIME 3 SET @time1 ='2017-02-22 08:03:00' 4 SET @time2 ='2017-02-22 08:09:00' 5 BEGIN 6 SELECT * FROM Record WHERE Time > @time1 AND Time < @time2 7 --判定特定时间段的成绩无效 8 UPDATE Record SET Sucess='False' WHERE Time > @time1 AND Time < @time2 9 SELECT * FROM Record WHERE Time > @time1 AND Time < @time2 10 END 11 GO
4.两表联合查询输出到一起（成绩有效者的时间，姓名，高度）
1 USE T4st 2 SELECT b.TIME,a.NAME,b.Height FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' ORDER BY b.TIME ASC 3 GO
5.两表联合查询输出到一起（主要排名冠亚季军）
1 USE T4st 2 SELECT c.NAME,c.SCORD,'冠军' AS DeJiang FROM ( 3 SELECT a.NAME,MAX(b.Height) AS SCORD, 4 ROW_NUMBER() over(order by a.NAME) as ROWS 5 FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '1' 6 UNION 7 SELECT c.NAME,c.SCORD,'亚军' AS DeJiang FROM ( 8 SELECT a.NAME,MAX(b.Height) AS SCORD, 9 ROW_NUMBER() over(order by a.NAME) as ROWS 10 FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '2' 11 UNION 12 SELECT c.NAME,c.SCORD,'季军' AS DeJiang FROM ( 13 SELECT a.NAME,MAX(b.Height) AS SCORD, 14 ROW_NUMBER() over(order by a.NAME) as ROWS 15 FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '3' 16 GO
运行结果如下：
