Sql语句求解

[Persistence_x]

学生表Stu
课程表KC
成绩表Scor

求2个sql

1:--计算出学生A比学生B成绩高的课目
2:--计算出每个功课最好的学生

谢谢！

[二月十六]

--测试数据
if not object_id(N'Tempdb..#Stu') is null
	drop table #Stu
Go
Create table #Stu([SNo] int,[Sname] nvarchar(23))
Insert #Stu
select 1,N'学生A' union all
select 2,N'学生B' union all
select 3,N'学生C'
GO
if not object_id(N'Tempdb..#KC') is null
	drop table #KC
Go
Create table #KC([TNo] int,[KCName] nvarchar(22))
Insert #KC
select 1,N'语文' union all
select 2,N'数学' union all
select 3,N'英语'
GO
if not object_id(N'Tempdb..#Scor') is null
	drop table #Scor
Go
Create table #Scor([SNo] int,[TNo] int,[ScoreF] int)
Insert #Scor
select 1,1,90 union all
select 1,2,85 union all
select 1,3,95 union all
select 2,1,89 union all
select 2,2,88 union all
select 2,3,98 union all
select 3,1,66 union all
select 3,2,75 union all
select 3,3,77
Go
--测试数据结束
--1、
WITH cte AS (
SELECT  #Scor.* ,
        Sname,
		KCName
FROM    #Stu
        JOIN #Scor ON #Scor.SNo = #Stu.SNo
		JOIN #KC ON #KC.TNo = #Scor.TNo
)
SELECT  a.KCName
FROM    cte a
        JOIN cte b ON a.ScoreF > b.ScoreF
                      AND b.KCName = a.KCName
WHERE   a.Sname = '学生A'
        AND b.Sname = '学生B'

--2、
SELECT  #KC.KCName ,
        #Stu.Sname
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY TNo ORDER BY ScoreF DESC ) AS rn
          FROM      #Scor
        ) t
        JOIN #Stu ON #Stu.SNo = t.SNo
        JOIN #KC ON #KC.TNo = t.TNo
WHERE   t.rn = 1

[Persistence_x]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#Stu') is null
	drop table #Stu
Go
Create table #Stu([SNo] int,[Sname] nvarchar(23))
Insert #Stu
select 1,N'学生A' union all
select 2,N'学生B' union all
select 3,N'学生C'
GO
if not object_id(N'Tempdb..#KC') is null
	drop table #KC
Go
Create table #KC([TNo] int,[KCName] nvarchar(22))
Insert #KC
select 1,N'语文' union all
select 2,N'数学' union all
select 3,N'英语'
GO
if not object_id(N'Tempdb..#Scor') is null
	drop table #Scor
Go
Create table #Scor([SNo] int,[TNo] int,[ScoreF] int)
Insert #Scor
select 1,1,90 union all
select 1,2,85 union all
select 1,3,95 union all
select 2,1,89 union all
select 2,2,88 union all
select 2,3,98 union all
select 3,1,66 union all
select 3,2,75 union all
select 3,3,77
Go
--测试数据结束
--1、
WITH cte AS (
SELECT  #Scor.* ,
        Sname,
		KCName
FROM    #Stu
        JOIN #Scor ON #Scor.SNo = #Stu.SNo
		JOIN #KC ON #KC.TNo = #Scor.TNo
)
SELECT  a.KCName
FROM    cte a
        JOIN cte b ON a.ScoreF > b.ScoreF
                      AND b.KCName = a.KCName
WHERE   a.Sname = '学生A'
        AND b.Sname = '学生B'

--2、
SELECT  #KC.KCName ,
        #Stu.Sname
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY TNo ORDER BY ScoreF DESC ) AS rn
          FROM      #Scor
        ) t
        JOIN #Stu ON #Stu.SNo = t.SNo
        JOIN #KC ON #KC.TNo = t.TNo
WHERE   t.rn = 1

这个with语句，不支持sqlserver吧

[二月十六]

with支持sqlserver，但是有版本限制，如果楼主的版本不支持，换成

--测试数据
if not object_id(N'Tempdb..#Stu') is null
    drop table #Stu
Go
Create table #Stu([SNo] int,[Sname] nvarchar(23))
Insert #Stu
select 1,N'学生A' union all
select 2,N'学生B' union all
select 3,N'学生C'
GO
if not object_id(N'Tempdb..#KC') is null
    drop table #KC
Go
Create table #KC([TNo] int,[KCName] nvarchar(22))
Insert #KC
select 1,N'语文' union all
select 2,N'数学' union all
select 3,N'英语'
GO
if not object_id(N'Tempdb..#Scor') is null
    drop table #Scor
Go
Create table #Scor([SNo] int,[TNo] int,[ScoreF] int)
Insert #Scor
select 1,1,90 union all
select 1,2,85 union all
select 1,3,95 union all
select 2,1,89 union all
select 2,2,88 union all
select 2,3,98 union all
select 3,1,66 union all
select 3,2,75 union all
select 3,3,77
Go
--测试数据结束
--1、

SELECT  a.KCName
FROM    ( SELECT    #Scor.* ,
                    Sname ,
                    KCName
          FROM      #Stu
                    JOIN #Scor ON #Scor.SNo = #Stu.SNo
                    JOIN #KC ON #KC.TNo = #Scor.TNo
        ) a
        JOIN ( SELECT   #Scor.* ,
                        Sname ,
                        KCName
               FROM     #Stu
                        JOIN #Scor ON #Scor.SNo = #Stu.SNo
                        JOIN #KC ON #KC.TNo = #Scor.TNo
             ) b ON a.ScoreF > b.ScoreF
                    AND b.KCName = a.KCName
WHERE   a.Sname = '学生A'
        AND b.Sname = '学生B'