数据库的 联合查询 子查询 排序函数
create database student
go
use student
go
create table UserInfo
(
stuID int primary key identity(1,1), --学生id
stuName varchar(20) not null, --学生姓名
sturoom int not null, --学生班级
stusex varchar(20) not null, --学生性别
)
create table Product
(
stuID int references UserInfo(stuID) not null, --学生id
score int not null, --考试成绩
stubject varchar(20) not null, --考试科目
)
insert into UserInfo values('张三',1,'男')
insert into UserInfo values('李四',2,'男')
insert into UserInfo values('王五',3,'女')
insert into Product values(1,80,'html')
insert into Product values(2,90,'jave')
insert into Product values(3,100,'c#')
select * from UserInfo
select * from Product
select * from UserInfo a inner join Product b on a.stuID=b.stuID
select stuName ,sturoom, score from UserInfo,Product where UserInfo.stuID=Product.stuID and score>80 and stuName='李四'
select stuName ,sturoom, score from UserInfo s1,( select * from Product where score>80) s2 where s1.stuID=s2.stuID and s1.stuName='王五'
select * from Product where stuID in(1,3)
select * from Product where stuID >all(select stuID from Product where stuID>1)
select * from Product where stuID >any(select stuID from Product where stuID>1)
select * from Product where stuID >some(select stuID from Product where stuID>1)
select * from Product where stuID>all(select stuID from Product )
select * from Product where stuID>any(select stuID from Product )
select * from Product where stuID>some(select stuID from Product )
select DENSE_RANK() over(partition by stubject order by score desc)as '排名', UserInfo. stuID,stuName,stubject from Product,UserInfo where UserInfo.stuID=Product.stuID
select ROW_NUMBER() over(order by score desc)as '排名',UserInfo.stuID,stuName from UserInfo,Product where UserInfo.stuID=Product.stuID and Product.stubject='html'
select rank() over(order by score desc)as '排名',UserInfo.stuID,stuName,stusex from UserInfo,Product where UserInfo.stuID=Product.stuID and Product.stubject='html'