高级查询

 

 

>[1]简单子查询

用T-SQL语句实现
declare @age int
set @age = stuage from stumarks where stuname = '李文才'
select * from stumarks where stuage>@age
go

select stuname from stuinfo inner join stumarks 
on stuinfo.stuno = stumarks.stuno 
where writtenexam = 60

用子查询语句实现
select * from stumarks
where stuage > (select stuage from stumarks where stuname = '李文才')-----子查询的返回值不能多余一个

select stuname from stuinfo
where stuno = (select stuno from stumarks where writtenexam = 60)

………………………………………………………………………………………………………………………………………………

>[2] in 和 not in 子查询

select stuname from stuinfo
where stuno in (select stuno from stumarks where writtenexam = 60)
go

select stuname from stuinfo
where stuno in (select stuno from stumarks)
go

select stuname from stuinfo
where stuno not in (select stuno from stumarks)
go

………………………………………………………………………………………………………………………………………………

>[3]exists 和 not exists

if exists (select * from sysdatabases where name = 'studb')
drop database studb
create database studb
(
 ………略………
)

if exists (select * from stumarks where writtenexam>80)
   begin
     print '本班有人笔试成绩超过80分，每人只加2分，加分后的成绩为：'
     updata stumarks set writtenexam = writtenexam + 2
     select * from stumarks
   end
else
   begin
     print '本班无人笔试成绩超过80分，每人加5分，加分后的成绩为：'
     updata stumarks set writtenexam = writtenexam + 5
     select * from stumarks
   end
go

if not exists (select * from stumarks where writtenexam>60 and labexam>60)
   begin
     print '本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：'
     updata stumarks set writtenexam = writtenexam + 3,labexam = labexam + 3
     select * from stumarks
   end
else
   begin
     print '本班考试成绩一般，每人只加1分，加分后的成绩为：'
     updata stumarks set writtenexam = writtenexam + 1,labexam = labexam + 1
     select * from stumarks
     end
go

………………………………………………………………………………………………………………………………………………

>[4]T-SQL 语句的综合应用

select * from stuinfo
select * from stumarks
select 应到人数 = (select count(*) from stuinfo),
实到人数 = (select count(*) from stumarks),
缺考人数 = ((select count(*) from stuinfo) - (select count(*) from stumarks))
if exists (select * from sysobjects where name = 'newtable')
drop table newtable
select stuname,stuno,writtenexam,labexam,
ispass = case
             when writtenexam>60 and labexam>60 then 1
             else 0
         end
into newtable from stuinfo left join stumarks
on stuinfo.stuno = stumarks.stuno
select * from newtable
declare @avgwritten numeric(4,1),@avglab numeric(4,1)
select @avgwritten = avg(writtenexam) from newtable where writtenexam is not null
select @avglab = avg(labexam) from newtable where labexam is not null
if @avgwritten<@avglab
while(1=1)
  begin
    update newtable set writtenexam = writtenexam + 1
  if (select max(writtenexam) from newtable)>=97
    break
  end
else
while(1=1)
  begin
    update newtable set labexam = labexam + 1
  if (select max(labexam) from newtable)>=97
    break
  end
update newtable set ispass = case
                                 when writtenexam>60 and labexam>60 then 1
                                 else 0
                             end
select * from newtable
select 姓名 = stuname,学号 = stuno,笔试成绩 = case
                                                  when writtenexam is null then '缺考'
                                                  else convert(varchar(5),writtenexam)
                                              end
，机试成绩 = case
                 when labexam is null then '缺考'
                 else convert(varchar(5),labexam)
             end
,是否通过 = case
                when ispass = 1 then '是'
                else '否'
      end
from newtable
select 总人数 = count(*),通过人数 = sum(ispass)
通过率 = (convert(varchar(5),avg(ispass*100)) + '%') from newtable