根据学号（学生表主键，成绩表外键）获取其成绩最高的科目

来源：百度知道编辑：UC知道时间：2024/07/03 12:20:52

请问如何根据学号（学生表主键，成绩表外键）获取其成绩最高的科目。数据表及关系代码如下：

/****** 对象: Table [dbo].[T_Students] 脚本日期: 08/17/2008 23:49:38 ******/

CREATE TABLE [dbo].[T_Students](
[StuID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_T_Students] PRIMARY KEY CLUSTERED
(
[StuID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** 对象: Table [dbo].[T_Scores] 脚本日期: 08/17/2008 23:49:45 ******/

CREATE TABLE [dbo].[T_Scores](
[ScoID] [int] IDENTITY(1,1) NOT NULL,
[StuID] [int] NULL,
[Subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Score] [int] NULL,
CONSTRAINT [PK_T_Scores] PRIMARY KEY CLUSTERED
(
[ScoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PGSTest]
GO
ALTER TABLE [dbo].[T_Scores] WITH CHECK ADD C

select a.Name, c.Subject
from T_Students a inner join
(select StuID, max(Score) as MaxScore
from T_Scores
group by StuID) as b
on a.StuID = b.StuID inner join T_Scores c
on b.MaxScore = c.Score and b.StuID = c.StuID

select t.StuID,(select max(Score) from T_Scores where StuID=t.StuID) from T_Student t group by StuID;

SELECT a.stuid, a.name, c.subject, c.score FROM T_students a INNER JOIN (SELECT MAX(score) AS score, stuid FROM T_scores GROUP BY stuid) b ON a.stuid = b.stuid INNER JOIN T_scores c ON a.stuid = b.stuid AND b.score = c.score