SQL deletes duplicate rows and queries all statements that are larger than a certain score analysis

With such a problem, a SQL statement is used to query the names of students who have more than 80 points per course.

Here is the table

Analysis, query each course is more than 80 students. SELECT DISTINCT name from dbo.student WHERE fenshu<=80 The result of the query is John Doe Harry Zhang San have.

Statements less than or equal to 80 select name from Dbo.student WHERE fenshu<=80 only 31.

This is repeated, and it is worthwhile to use the words in the not-in (no-time) statement to spell the two sentences as follows:

SELECT DISTINCT name from dbo.student WHERE name is not in (
SELECT name from Dbo.student WHERE fenshu<=80)

Now there is only the desired result (note that the result of the query is three Harry, you need to use distinct to eliminate duplicate rows).

Second question: Delete the same student redundancy information except the automatic numbering, the table is as follows:

Do this problem we see the ID is not the same, the others are the same, then you need to group, the statement: SELECT MAX (ID) from the Dbo.student1 Group by Grade,kecheng,name,x_id,course Group, And get the maximum ID. The fields after the group by statement must be the same before a group is generated.

Delete a statement after grouping

DELETE from Dbo.student1 WHERE ID not in (
SELECT MAX (ID) from Dbo.student1 GROUP by Grade,kecheng,name,x_id,course)

Delete the time by the ID of the condition, not the sub-condition of the statement to delete, and finally get their own ideal table.

SQL deletes duplicate rows and queries all statements that are larger than a certain score analysis