2020-10-28

数据库的       联合查询   子查询    排序函数   

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'