34,590
社区成员
发帖
与我相关
我的任务
分享
select * from class1
where 语文 in
(select max(语文) as 语文 from class1)
and 数学 in
(select max(数学) as 数学 from class1)
and 英语 in
(select max(英语) as 英语 from class1)
and 政治 in
(select max(政治) as 政治 from class1)
DECLARE @t1 TABLE(NAME NVARCHAR(10),YW INT,SX INT,EN INT,ZZ INT)
INSERT INTO @t1
SELECT '张三',90,98,78,87 UNION ALL
SELECT '李四',91,92,80,89 UNION ALL
SELECT '王毛',89,79,72,60 UNION ALL
SELECT '张三',93,92,99,80
DECLARE @t2 TABLE(NAME NVARCHAR(10),YW INT,SX INT,EN INT,ZZ INT)
INSERT INTO @t2
SELECT '王二',90,98,78,87 UNION ALL
SELECT '李林',91,92,80,89 UNION ALL
SELECT '王林',100,79,72,60 UNION ALL
SELECT '张小',93,100,99,80
SELECT NAME,YW=CASE WHEN YW=(SELECT MAX(YW) FROM (SELECT * FROM @t1 UNION ALL SELECT * FROM @t2) a) THEN YW ELSE NULL END,
SX=CASE WHEN SX=(SELECT MAX(SX) FROM (SELECT * FROM @t1 UNION ALL SELECT * FROM @t2) a) THEN SX ELSE NULL END,
EN=CASE WHEN EN=(SELECT MAX(EN) FROM (SELECT * FROM @t1 UNION ALL SELECT * FROM @t2) a) THEN EN ELSE NULL END,
ZZ=CASE WHEN ZZ=(SELECT MAX(ZZ) FROM (SELECT * FROM @t1 UNION ALL SELECT * FROM @t2) a) THEN ZZ ELSE NULL END
FROM (SELECT * FROM @t1 UNION ALL
SELECT * FROM @t2) b
declare @class1 table (姓名 nvarchar(10),语文 int,数学 int,英语 int, 政治 int)
insert into @class1 select '张三',90,98,78,87
union all select '李四',91,92,80,89
union all select '王毛',89,79,72,60
union all select '张丰',93,92,99,80
--select * from @class1
declare @class2 table (姓名 nvarchar(10),语文 int,数学 int,英语 int, 政治 int)
insert into @class2 select '王二',90,91,78,87
union all select '李林',94,96,99,100
union all select '王林',89,79,72,60
union all select '张小',93,92,89,80
select 'class1' as 班级, 第一名='1',isnull(姓名,'') 排名次 from
(select 姓名,RANK()over(order by 语文 desc) as 语文排名,RANK()over(order by 数学 desc) as 数学排名,
rank() over(order by 英语 desc) as 英语排名,rank()over(order by 政治 desc) as 政治排名
from @class1) a where 语文排名=1 and 数学排名=1 and 英语排名=1 and 政治排名=1
union all
select 'class2' as 班级, 第一名=1, isnull(姓名,'')
from(select 姓名,RANK()over(order by 语文 desc) as 语文排名,RANK()over(order by 数学 desc) as 数学排名,
rank() over(order by 英语 desc) as 英语排名,rank()over(order by 政治 desc) as 政治排名
from @class2) a where 语文排名=1 and 数学排名=1 and 英语排名=1 and 政治排名=1
class2 1 李林