作者somnolentcat (普小莉)
标题Re: [SQL ] SQL练习题
时间Wed Jan 15 19:01:42 2014
a...问题里面没看到你要求平均分数是求什麽的平均
可是你的SQL里面有...企划案不写清楚无法作业啊(哭哭)
我以下当作个人平均分数喔，请试试看～ ≧▽≦
以下，MSSQL语法，直接整个全部COPY去贴，范例只各年级取1个
TOP自己改，应该会吧？
if exists
(select * from dbo.sysobjects where id = object_id('[dbo].[捞资料]')and
objectproperty(id,'IsProcedure') = 1)
drop PROCEDURE [dbo].[捞资料]
GO
CREATE PROCEDURE [dbo].[捞资料]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE cur CURSOR LOCAL FOR
select distinct 年级 from 学生
DECLARE @id_no nvarchar(50)
BEGIN
OPEN cur
FETCH NEXT FROM cur INTO @id_no
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 A.[学号],a.[姓名],a.[年级],SUM(B.分数) as 总分
,AVG(B.分数) AS 平均成绩
FROM [test].[dbo].[学生] a inner join 成绩 b on a.学号 = b.学号
where [年级]=@id_no
GROUP BY a.年级,a.学号,a.[姓名]
order by NEWID()
FETCH NEXT FROM cur INTO @id_no
END
CLOSE cur --关闭cur
DEALLOCATE cur --释放cur
END
END
if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[学生]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[学生]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[成绩]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[成绩]
GO
CREATE TABLE [dbo].[学生](
[学号] [int] IDENTITY(1,1) NOT NULL,
[姓名] [nvarchar](50) NOT NULL,
[年级] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED
(
[学号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[成绩](
[编号] [int] IDENTITY(1,1) NOT NULL,
[学号] [int] NOT NULL,
[科目] [nvarchar](50) NOT NULL,
[分数] [int] NOT NULL,
CONSTRAINT [PK_成绩] PRIMARY KEY CLUSTERED
(
[编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON [PRIMARY]
GO
insert [学生]( 姓名,年级) values('A01',1)
insert [学生]( 姓名,年级) values('A02',2)
insert [学生]( 姓名,年级) values('A03',1)
insert [学生]( 姓名,年级) values('A04',1)
insert [学生]( 姓名,年级) values('A05',3)
insert [学生]( 姓名,年级) values('A06',1)
insert [学生]( 姓名,年级) values('A07',1)
insert [学生]( 姓名,年级) values('A08',2)
insert [学生]( 姓名,年级) values('A09',3)
insert [学生]( 姓名,年级) values('A10',1)
insert [成绩]( 学号,科目,分数) values(1,'国文',60)
insert [成绩]( 学号,科目,分数) values(1,'英文',77)
insert [成绩]( 学号,科目,分数) values(1,'数学',30)
insert [成绩]( 学号,科目,分数) values(2,'国文',40)
insert [成绩]( 学号,科目,分数) values(2,'英文',87)
insert [成绩]( 学号,科目,分数) values(2,'数学',39)
insert [成绩]( 学号,科目,分数) values(3,'国文',55)
insert [成绩]( 学号,科目,分数) values(3,'英文',79)
insert [成绩]( 学号,科目,分数) values(3,'数学',90)
insert [成绩]( 学号,科目,分数) values(4,'国文',60)
insert [成绩]( 学号,科目,分数) values(4,'英文',77)
insert [成绩]( 学号,科目,分数) values(5,'数学',30)
insert [成绩]( 学号,科目,分数) values(6,'国文',60)
insert [成绩]( 学号,科目,分数) values(7,'英文',77)
insert [成绩]( 学号,科目,分数) values(7,'数学',30)
insert [成绩]( 学号,科目,分数) values(8,'国文',60)
insert [成绩]( 学号,科目,分数) values(9,'英文',77)
insert [成绩]( 学号,科目,分数) values(10,'数学',30)
insert [成绩]( 学号,科目,分数) values(10,'国文',60)
insert [成绩]( 学号,科目,分数) values(10,'英文',77)
DECLARE @RC int
-- TODO: 在此处设定参数值。
EXECUTE @RC = [捞资料]
噢，我的下班时间啊orz
※ 引述《john9872 (菸酒生)》之铭言：
: 假设我有两个资料表，分别为"学生"及"成绩"，
: 资料栏位分别为: ( student_ID, 姓名, 年级 ) 及
: ( 成绩流水号, student_ID, 科目, 分数 )，
: 资料内容如下图例：
: "学生"资料表 "成绩"资料表
: ------------- -------------
: student_ID 姓名 年级 成绩流水号 student_ID 科目 分数
: ============================ ====================================
: 1 小黄 1 1 1 国文 60
: 2 小白 2 2 1 数学 70
: 3 小虎 3 3 1 英文 50
: 4 小便 4 5 2 国文 12
: . . . . . .
: . . . . . .
: . . . . . .
: 假设学生资料表共有900笔(共有1~6年级)，成绩资料表1800笔(国英数3科)，
: 请问要怎麽从1~6年级各挑出100名学生(共600名)，并计算出他们的成绩及总分呢?
: 以下是我写的SQL:
: SELECT student_ID, SUM(分数), AVG(分数)
: FROM 学生
: GROUP BY student_ID
: ....
: 由於我是SQL初心者，所以太复杂的SQL语法还不太能理解，
: 目前是卡在要取各年级100名学生的部分，
: 不知道该如何运用WHERE或其他指令写出来(题目也有提示用JOIN)
: 麻烦大家替我解惑，谢谢各位：)
--
没钱时不变心可以证明一个女人是否对你真心，
有钱时不变心可以证明一个男人对你是否真心。
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 220.130.112.242