作者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