Home  >  Article  >  Database  >  sql 数据库知识

sql 数据库知识

WBOY
WBOYOriginal
2016-06-07 17:43:37929browse

现有一数据表test,结构如下 studentnamecoursenamemark aa80 ab60 ac30 ba40 bb50 bc58 ca60 cb70 cc88 da90 db70 dc88 1 查出每门功课的前一名(并列名次问题) select * from ( select *,ROW_NUMBER() over(partition by coursename order by mark desc)

现有一数据表test,结构如下

studentname coursename mark

a a 80

a b 60

a c 30

b a 40

b b 50

b c 58

c a 60

c b 70

c c 88

d a 90

d b 70

d c 88

1 查出每门功课的前一名(并列名次问题)


  select * from (
  select *,ROW_NUMBER() over(partition by coursename order by mark desc) ranks 
  from test_a
  ) a
  where a.ranks=1


  partition by :分区;按coursename分区,依据mark排序 即可得到每门课程的第一名,香港虚拟主机,同理改变条件可获得前三名、前五名等。

2 查出至少两门功课达到60分的学生

  select a.studentname from (
    select studentname,coursename
    from test_a where mark>60
  ) a
  group by a.studentname
  having count(a.coursename)>=2

 


  

 

,美国空间,香港空间
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn