sql 建表以及查询---复杂查询之成绩排名

废话不说，直接建表

1.表Player

USE T4st -- 设置当前数据库为T4st,以便访问sysobjects
IF EXISTS(SELECT * FROM sysobjects WHERE name='Player')
DROP TABLE Player
CREATE TABLE Player
(
  Code VARCHAR(4) PRIMARY KEY NOT NULL,
  Name VARCHAR(50),
)
INSERT INTO Player VALUES('0001','赵甲');
INSERT INTO Player VALUES('0002','钱乙');
INSERT INTO Player VALUES('0003','孙丙');
INSERT INTO Player VALUES('0004','李丁');
INSERT INTO Player VALUES('0005','周戊');
INSERT INTO Player VALUES('0006','吴已');
INSERT INTO Player VALUES('0007','郑庚');
INSERT INTO Player VALUES('0008','王辛');
INSERT INTO Player VALUES('0009','冯壬');
INSERT INTO Player VALUES('0010','陈葵');

SELECT * FROM Player
GO

2.表Record

USE T4st -- 设置当前数据库为T4st,以便访问sysobjects
IF EXISTS(SELECT * FROM sysobjects WHERE name='Record')
DROP TABLE Record
CREATE TABLE Record
(
  Time DATETIME PRIMARY KEY NOT NULL,
  Code VARCHAR(4),
  Height VARCHAR(5),
  Sucess VARCHAR(5)
)
INSERT INTO Record VALUES('2017-02-22 08:01:11','0001','1.80','False');
INSERT INTO Record VALUES('2017-02-22 08:02:32','0005','1.71','True');
INSERT INTO Record VALUES('2017-02-22 08:03:25','0001','1.81','True');
INSERT INTO Record VALUES('2017-02-22 08:04:22','0008','1.90','False');
INSERT INTO Record VALUES('2017-02-22 08:05:15','0008','1.90','True');
INSERT INTO Record VALUES('2017-02-22 08:07:06','0001','1.90','False');
INSERT INTO Record VALUES('2017-02-22 08:08:19','0003','1.91','True');
INSERT INTO Record VALUES('2017-02-22 08:09:18','0002','1.92','True');
INSERT INTO Record VALUES('2017-02-22 08:10:48','0005','1.81','True');
INSERT INTO Record VALUES('2017-02-22 08:12:36','0010','1.79','False');

SELECT * FROM Record
GO

3.判定特定时间段的成绩无效

USE T4st
DECLARE @time1 DATETIME,@time2 DATETIME
SET @time1 ='2017-02-22 08:03:00'
SET @time2 ='2017-02-22 08:09:00'
BEGIN
   SELECT * FROM Record WHERE Time > @time1 AND Time < @time2 
   --判定特定时间段的成绩无效
   UPDATE Record SET Sucess='False' WHERE Time > @time1 AND Time < @time2 
   SELECT * FROM Record WHERE Time > @time1 AND Time < @time2
END
GO

4.两表联合查询输出到一起（成绩有效者的时间，姓名，高度）

USE T4st
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
GO

5.两表联合查询输出到一起（主要排名冠亚季军）

USE T4st
SELECT c.NAME,c.SCORD,'冠军' AS DeJiang FROM (
SELECT a.NAME,MAX(b.Height) AS SCORD,
   ROW_NUMBER() over(order by a.NAME) as ROWS  
  FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '1'
UNION
SELECT c.NAME,c.SCORD,'亚军' AS DeJiang FROM (
SELECT a.NAME,MAX(b.Height) AS SCORD,
   ROW_NUMBER() over(order by a.NAME) as ROWS  
  FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '2'
UNION
SELECT c.NAME,c.SCORD,'季军' AS DeJiang FROM (
SELECT a.NAME,MAX(b.Height) AS SCORD,
   ROW_NUMBER() over(order by a.NAME) as ROWS  
  FROM Player a , Record b WHERE a.Code = b.Code AND b.Sucess='True' GROUP BY a.NAME) c WHERE c.ROWS = '3'
GO

 运行结果如下：