34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH TB AS(
SELECT 1001 学号,85 成绩,'语文' 课程名
UNION ALL SELECT 1001,90,'数学'
UNION ALL SELECT 1002,70,'语文'
UNION ALL SELECT 1002,100,' 外语'
UNION ALL SELECT 1003,95,'语文'
UNION ALL SELECT 1003,80,'数学'
UNION ALL SELECT 1004,95,'语文'
UNION ALL SELECT 1004,80,'数学')
select 学号,SUM(case when 课程名 = '语文' then 成绩 else 0 end) [语文成绩]
, SUM(case when 课程名 = '数学' then 成绩 else 0 end) [数学成绩]
from TB group by 学号
having SUM(case when 课程名 = '语文' then 成绩 else 0 end) > SUM(case when 课程名 = '数学' then 成绩 else 0 end)
/*
学号 语文成绩 数学成绩
1002 70 0
1003 95 80
1004 95 80
*/
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(学号 int, 成绩 int, 课程名 varchar(8))
insert into #
select 1001, 85, '语文' union all
select 1001, 90, '数学' union all
select 1002, 70, '语文' union all
select 1002, 100, '外语' union all
select 1003, 95, '语文' union all
select 1003, 80, '数学' union all
select 1004, 95, '语文' union all
select 1004, 80, '数学'
select * from # as t where 课程名 = '语文' and not exists (select 1 from # where 学号=t.学号 and 课程名='数学' and 成绩>t.成绩)
/*
学号 成绩 课程名
----------- ----------- --------
1002 70 语文
1003 95 语文
1004 95 语文
*/
--> 测试数据:Stu
if object_id('Stu') is not null
drop table Stu
---->建表
create table Stu([学号] int,[成绩] int,[课程名] varchar(4))
insert Stu
select 1001,85,'语文' union all
select 1001,90,'数学' union all
select 1002,70,'语文' union all
select 1002,100,'外语' union all
select 1003,95,'语文' union all
select 1003,80,'数学' union all
select 1004,95,'语文' union all
select 1004,80,'数学'
--> 查询结果
SELECT a.*,b.课程名,b.成绩 FROM Stu a,Stu b
where a.学号 = b.学号 and a.成绩>b.成绩
and a.课程名='语文'
--> 删除表格
--DROP TABLE Stu
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([学号] int,[成绩] int,[课程名] varchar(4))
insert [tb]
select 1001,85,'语文' union all
select 1001,90,'数学' union all
select 1002,70,'语文' union all
select 1002,100,'外语' union all
select 1003,95,'语文' union all
select 1003,80,'数学' union all
select 1004,95,'语文' union all
select 1004,80,'数学'
---查询---
select distinct a.学号
from tb a,tb b
where a.学号=b.学号
and a.课程名='数学' and b.课程名='语文'
and a.成绩<b.成绩
---结果---
学号
-----------
1003
1004
(2 行受影响)
select 学号 from TB group by 学号
having SUM(case when 课程名 = '语文' then 成绩 else 0 end) > SUM(case when 课程名 = '数学' then 成绩 else 0 end)