SQL database knowledge

Source: Internet
Author: User
There is a data table named test. The structure is as follows: locate the first (parallel ranking problem) of each course select * from (select *, ROW_NUMBER () over (partitionbycoursenameorderbymarkdesc)

The existing test data table has the following structure: studentnamecoursenamemark aa80 ab60 ac30 ba40 bb50 bc58 ca60 cb70 cc88 da90 db70 dc88 1. Find out the first name (parallel ranking problem) of each course select * from (select *, ROW_NUMBER () over (partition by coursename order by mark desc)

The existing data table test has the following structure:

Studentnamecoursenamemark

Aa80

Ab60

Ac30

Ba40

Bb50

Bc58

Ca60

Cb70

Cc88

Da90

Db70

Dc88

1. Find out the first place in each course (parallel ranking problem)


Select * from (
Select *, ROW_NUMBER () over (partition by coursename order by mark desc) ranks
From test_a
)
Where a. ranks = 1


Partition by: partition. by coursename, you can get the first place in each course by mark. The Hong Kong virtual host can also be the top three or top five if you change the condition.

2. Find out at least two students whose homework score is 60.

Select a. studentname from (
Select studentname, coursename
From test_a where mark> 60
)
Group by a. studentname
Having count (a. coursename)> = 2


  

, US space, Hong Kong Space

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.