求教， sql视图有关问题

求教， sql视图问题
有下述sql语句：
use master
 go
 if exists(select * from sysdatabases where user='tempdatabase')
 drop database tempdatabase
 go
 create database tempdatabase
 go
 use tempdatabase
create table records
 (
 studentname varchar(50), -- 学生姓名
 subject char(4), -- 科目
 score int -- 成绩
 )
 delete records
 insert into records values('p1','c1',44)
 insert into records values('p2','c1',87)
 insert into records values('p3','c1',65)
 insert into records values('p4','c1',29)
 insert into records values('p1','c2',13)
 insert into records values('p2','c2',32)
 insert into records values('p3','c2',44)
 insert into records values('p4','c2',34)
 insert into records values('p1','c3',35)
 insert into records values('p2','c3',56)
 insert into records values('p3','c3',43)
insert into records values('p4','c3',23)

1、请写出一个成绩单视图（视图名可用：ChengJiDan或英文）的创建sql，视图功能为，得出每科成绩最高分的同学的姓名，科目及分数

2、请写出平均分第一名的查询sql，列出平均成绩及姓名

请教大神，小弟刚学sql,不懂

[解决办法]

USE test
GO

--use master
-- go
--if exists(select * from sysdatabases where user='tempdatabase')
--drop database tempdatabase
--go
--create database tempdatabase
--go
--use tempdatabase
if object_id('records')IS NOT NULL
DROP TABLE records
create table records
(
studentname varchar(50), -- 学生姓名
subject char(4), -- 科目
score int -- 成绩
)
delete records
insert into records values('p1','c1',44)
insert into records values('p2','c1',87)
insert into records values('p3','c1',65)
insert into records values('p4','c1',29)
insert into records values('p1','c2',13)
insert into records values('p2','c2',32)
insert into records values('p3','c2',44)
insert into records values('p4','c2',34)
insert into records values('p1','c3',35)
insert into records values('p2','c3',56)
insert into records values('p3','c3',43)
insert into records values('p4','c3',23)

Go


--------------- 1.

IF object_id('v_ChengJiDan')IS NOT NULL
DROP VIEW v_ChengJiDan
Go
CREATE VIEW v_ChengJiDan 
AS 

SELECT
studentname
,subject
,score
FROM records AS a
WHERE NOT EXISTS(SELECT 1 FROM records AS x
WHERE x.subject=a.subject
AND x.score>a.score
)

Go

SELECT * FROM v_ChengJiDan
/*
studentname  subject score
------------ ------- ------
p2           c1      87
p3           c2      44
p2           c3      56 
 
*/


--------------- 2.

SELECT 
avg_score,studentname 
FROM (
SELECT 
AVG(score)AS avg_score
,studentname 
FROM records 
GROUP BY studentname
) AS a
WHERE NOT EXISTS(SELECT 1 FROM (
SELECT 
AVG(score)AS avg_score
,studentname 
FROM records 
GROUP BY studentname
) AS x
WHERE x.avg_score>a.avg_score
)
/*
avg_score   studentname
----------- -----------
58          p2
*/

[解决办法]
CREATE VIEW V_ChengJiDan
AS
SELECT a.studentname,b.* FROM dbo.records a
INNER JOIN 
(
SELECT subject,MAX(score) score
FROM records 
GROUP BY subject
) b
ON a.score=b.score
GO



SELECT a.studentname,b.* FROM dbo.records a
INNER JOIN 
(
SELECT TOP(1) subject,AVG(score) score
FROM records 
GROUP BY subject
ORDER BY score DESC
) b
ON a.score=b.score

[解决办法]

SELECT * FROM records a WHERE NOT EXISTS (SELECT 1 FROM records WHERE [subject]=a.[subject] AND score>a.score)
 
 SELECT TOP 1 WITH ties  studentname,AVG(score) FROM records GROUP BY studentname ORDER BY AVG(score) desc

查看更多 下一篇