SQL查询（交、并、子查询、开窗函数……）

• select * from scores

  

• 使用TOP限制结果集，WITH TIES作用-包括最后一行取值并列的结果
  

  --查询english最高的前3名
  select top 3 with ties id,chinese,math,english
  from scores 
  order by english desc

  

  --查询出现次数最多的chinese分数值
  select top 1 with ties chinese, count(*) as 次数 --COUNT()需要和group by一起使用
  from scores
  group by chinese
  order by count(*) desc

  
  
  
  

  --查询总成绩前20%学生成绩
  select top 20 percent with ties id, chinese, math, english, chinese+math+english as total
  from scores 
  order by chinese+math+english desc

  

• select  id, chinese, math, english, chinese+math+english as total, level = 
  case
      when chinese+math+english > 270 then 'A'
      when chinese+math+english > 230 then 'B'
      else 'C'
  end
  from scores 
  order by chinese+math+english desc

  

• --将查询结果保存到新表中
  --使用'#'将新表标识临时表，生存期与创建此局部临时表的用户的连接生存期相同，只在当前连接中使用
  --使用'##'将新表标识全局临时表，生存期与创建此局部临时表的用户的连接生存期相同，可被所有的连接使用
  --将上一步查询的结果存入新表
  select  id, chinese, math, english, chinese+math+english as total, level = 
  case
      when chinese+math+english > 270 then 'A'
      when chinese+math+english > 230 then 'B'
      else 'C'
  end
  into global_scores --全局新表
  from scores 
  order by chinese+math+english desc
  go 
  select * from global_scores

  
  
  

  --在永久表中查询level为B的学生
  select * from global_scores
  where level = 'B'

  
  
  

  --将查询结果保存到新表中
  --使用'#'将新表标识临时表，生存期与创建此局部临时表的用户的连接生存期相同，只在当前连接中使用
  --使用'##'将新表标识全局临时表，生存期与创建此局部临时表的用户的连接生存期相同，可被所有的连接使用
  --将上一步查询的结果存入新表
  select  id, chinese, math, english, chinese+math+english as total, level = 
  case
      when chinese+math+english > 270 then 'A'
      when chinese+math+english > 230 then 'B'
      else 'C'
  end
  into #temp_scores --临时表
  from scores 
  order by chinese+math+english desc
  go 
  select * from  #temp_scores --可像永久表一样查询
  where level = 'B'

  

• 并运算UNION
  

  select * from global_scores
  where level = 'B'
  union
  select * from global_scores
  where level = '

  

• 交运算INTERSECT
  

  select * from scores where chinese = 95
  intersect
  select * from scores where math = 64

  

• 差运算EXCEPT
  

  select * from scores where chinese = 95

  

  select * from scores where chinese = 95
  intersect
  select * from scores where math = 64

  
• 使用子查询进行基于集合的测试

  --子查询,查询与id为2的学生chinese成绩相同的学生
  select * from scores
  where chinese in
  (
      select chinese from scores where id = 2
  )
  and id != 2

  

• 使用子查询进行比较测试

• --查询成绩最高的
  select *, chinese+math+english as total
  from scores where chinese+math+english =
  (
      select MAX(chinese+math+english) from scores
  )

  

  --查询成绩高与平均分的学生
  select *, chinese +math + english as total from scores
  where chinese + math + english >
  (
      select AVG(chinese +math + english) from scores
  )

  

•  使用子查询进行存在性测试

  select * from info

  

  --查询名字为'd'的学生成绩
  select * from scores 
  where exists
  (
      select * from info where scores.id = info.id and info.name = 'd'
  )

  

  
  以下情况起不到选择作用，会返回所有结果

  select * from scores 
  where exists
  (
      select * from scores where chinese > 80
  )

  

  
  改成这样则可以，可能因为子查询只查一个表的时候，会导致exists返回的值总是true或总是false的情况（对于每一层外查询，exists内执行的子查询是一样的），而与外查询中的表连接后，因为每一层外查询对应的值不同，所以每一次执行的子查询结果都不同，exists会有true或false，达到选择的目的
  
  如上面一个查询语句中的select * from scores where chinese > 80，该语句总是true，因为一直存在>80的数据，而对于下面这一查询语句，因为外查询是一行一行查的，对于每一行外查询，它的id值都是不同的（依次为1,2,3,4……），所以子查询中的select * from scores b where a.id = b.id and chinese > 80，条件：a.id = b.id并没有一直成立，所以可能返回true或false

  select * from scores a 
  where exists
  (
      select * from scores b where a.id = b.id and chinese > 80
  )

  
  
  以下数据库数据借用：https://blog.csdn.net/mrbcy/article/details/68965271

• 替代表达式的子查询
  

  --替代表达式的子查询是指在SELECT语句的选择列表中嵌入一个只返回一个标量值的SELECT语句，这个查询语句通常是通过一个聚合函数来返回一个单值
  --以下查询名为李军的学生所选课程数目
  select sno, sname,
  (
      select COUNT(*) from students 
      join scores on students.sno = scores.sno
      where students.sname = '李军'
      )as course_cnt
  from students
  where students.sname = '李军'

• 派生表
  

  --派生表（内联视图）是将子查询作为一个表来处理，这个由子查询产生的新表就被称为“派生表”，类似于临时表
  --一下查询同时操作系统和计算机导论的学生
  select students.sno,students.sname
  from
  (
      select * from scores 

  
   7     where scores.cno = (select cno from courses where courses.cname = '操作系统')
  )as t1
  join 
  (
      select * from scores
      where scores.cno = (select cno from courses where courses.cname = '计算机导论')
  )as t2
  on t1.sno = t2.sno
  join students on t1.sno = students.sno

  

  当然也可用以下查询实现

  select sno,sname from students where sno in (
  select sno from scores where cno = (select cno from courses where courses.cname = '计算机导论'))
  and sno in (
  select sno from scores where cno = (select cno from courses where courses.cname = '操作系统'))

  

   返回结果是相同的

• 开窗函数
  一组行被称为一个窗口，开窗函数是指可以用于“分区”或“分组”计算的函数，这些函数结核OVER字句对组内的数据进行编号，并进行求和、计算平均值等统计。
  如SUM、AVG等函数
  
  • 将OVER字句与聚合函数结合使用
    

    • --查询每门课的平均分，over作用：按课号分好组，然后求平均分
      select distinct courses.cno, cname,CAST( 1.0*AVG(degree)over(partition by scores.cno)as decimal(5,2)) as avg_degree
      from courses join scores on courses.cno = scores.cno 
      
      --大小为5，保留两位小数
      --as decimal(5,2)
      
      --cast(xxx as decimal)

      

  • 将OVER字句与排名函数一起使用　
    • 排名函数为分区中的每一行返回一个排名值，根据所用函数的不同，某些行可能与其他行具有相同的排名值，排名函数具有不确定性。
      
      • RANK()函数：相同的值会有相同排名
        

        --查询各学生的总分班级排名
        select *, chinese+math+english as total,
        RANK() over
        (partition by class order by chinese+math+english desc) as class_rank
        from scores
        order by chinese+math+english desc

        

      • DENSE_RANK()：用法同RANK()，但排名的取值是连续的

        --DENSE_RANK查询各学生的总分班级排名
        select *, chinese+math+english as total,
        DENSE_RANK() over
        (partition by class order by chinese+math+english desc) as class_rank
        from scores
        order by chinese+math+english desc

        注意最后一个学生的名次，与RANK比较

        

      • NTILE()：分组
        

        --NTILE：将学生按成绩划分到3个组中
        select *, chinese+math+english as total,
        NTILE(3) over
        (order by chinese+math+english desc) as ngroup
        from scores

        
        
        

        --NTILE：将学生以班级为单位按成绩划分到3个组中
        select *, chinese+math+english as total,
        NTILE(3) over
        (partition by class order by chinese+math+english desc) as ngroup
        from scores

        

      • ROW_NUMBER：返回结果集中每个分区内行的序列号
        

        select *, chinese+math+english as total,
        ROW_NUMBER() over
        (PARTITION BY class order by chinese+math+english desc) as ngroup
        from scores

        

• 公用表表达式
  将查询语句产生的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表表达式　　　　　　

  --查询时显示学生所在班级人数
  with class_cnt(class, cnt) as 
  (
      select class,count(*) from scores
      group by class)
  select scores.*,class_cnt.cnt as class_cnt from scores
  join class_cnt on scores.class = class_cnt.class