--表
Student (StudentID, StudentName, Grender, Age, ClassID)
Class (ClassID, ClassName)--表数据
--Student表
2003300201 李四 0 20 1
2003300202 王五 0 21 1
2003300203 卢芬 1 18 1
2003300301 王鸣 0 21 2
2003300302 张红 1 18 2
--Class表
1 计算机软件开发01班
2 计算机软件开发02班 -- 问题:使用模糊查询所有班级名称包含"计算机"的学生,并且年龄最大的男生信息(StudentID,StudentName,Grender ,Age,ClassName)
--需要的结果
2003300202 王五 0 21 计算机软件开发01班
2003300301 王鸣 0 21 计算机软件开发02班
Student (StudentID, StudentName, Grender, Age, ClassID)
Class (ClassID, ClassName)--表数据
--Student表
2003300201 李四 0 20 1
2003300202 王五 0 21 1
2003300203 卢芬 1 18 1
2003300301 王鸣 0 21 2
2003300302 张红 1 18 2
--Class表
1 计算机软件开发01班
2 计算机软件开发02班 -- 问题:使用模糊查询所有班级名称包含"计算机"的学生,并且年龄最大的男生信息(StudentID,StudentName,Grender ,Age,ClassName)
--需要的结果
2003300202 王五 0 21 计算机软件开发01班
2003300301 王鸣 0 21 计算机软件开发02班
create table #t(name varchar(50),classname int,age int)
insert into #t
select '张三' as name , '1' as classname , 19 as age union all
select '李四','1',20 union all
select '123','2',20 union all
select '222','2',24 union all
select '333','2',28 select * from #t a
where not exists
(select 1 from #t where a.classname = classname and age > a.age)
--and classname in(计算机客户)
drop table #t
------------------------------------------
name classname age
-------------------------------------------------- ----------- -----------
李四 1 20
333 2 28(2 行受影响)
*
from Student as s
inner join Class as c on s.ClassID=c.Class
where c.ClassName like '%计算机%'
insert into #t
select '张三' as name , '1' as classname , 19 as age union all
select '李四','1',20 union all
select '123','2',20 union all
select '222','2',24 union all
select '333','2',28 select name,classname,age
from
(
select
row_number() over(partition by classname order by age desc) as flag,* from #t
)a
where flag = 1drop table #t
if not object_id('Tempdb..#Student') is null
drop table #Student
Go
Create table #Student([StudentID] int,[StudentName] nvarchar(2),[Grender] int,[Age] int,[ClassID] int)
Insert #Student
select 2003300201,N'李四',0,20,1 union all
select 2003300202,N'王五',0,21,1 union all
select 2003300203,N'卢芬',1,18,1 union all
select 2003300301,N'王鸣',0,21,2 union all
select 2003300302,N'张红',1,18,2
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#Class') is null
drop table #Class
Go
Create table #Class([ClassID] int,[ClassName] nvarchar(10))
Insert #Class
select 1,N'计算机软件开发01班' union all
select 2,N'计算机软件开发02班'
Go
select
c.*,s.*
from #Student as s
inner join #Class as c on s.ClassID=c.ClassID
where c.ClassName like '%计算机%' and not exists(select 1 from #Student where ClassID=s.ClassID and [Age]>s.[Age])
on a.ClassID=b.ClassID
c.*,s.*
from #Student as s
inner join #Class as c on s.ClassID=c.ClassID
where c.ClassName like '%计算机%' and [StudentID]=(select top 1 [StudentID] from #Student where ClassID=s.ClassID order by [Age] desc)
Age相同时也显示一条时用StudentID判断