SQL Server r2--row_number () removes duplicate content from the same column in the same row

usetempdbGO--------------------------------------------------------------------------IF object_id('Student','U') is  not NULLDROP TABLEStudentGOCREATE TABLEStudent (StuidNVARCHAR(8)PRIMARY KEY, NameNVARCHAR(5) )GOINSERT  intoStudent (Stuid,name)VALUES('20080001','Lily')INSERT  intoStudent (Stuid,name)VALUES('20080002','Lucy')INSERT  intoStudent (Stuid,name)VALUES('20080003','Jack')GO--------------------------------------------------------------------------IF object_id('Sltcourse','U') is  not NULLDROP TABLESltcourseGOCREATE TABLESltcourse--Selectivecourse(IDINT PRIMARY KEY IDENTITY(1,1), StuidNVARCHAR(8), CoursenameNVARCHAR(Ten), scoreINT )GOINSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080003','Computer Repair', -)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080003','Paper Cutting', the)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080003','Market Planning', the)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080003','Information Retrieval', -)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080001','Flower Arrangement', About)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080001','Paper Cutting', the)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080001','Embroidery', the)INSERT  intoSltcourse (Stuid,coursename,score)VALUES('20080002','Flower Arrangement',98)GO--------------------------------------------------------------------------SELECT *  fromStudentGOSELECT *  fromSltcourseGO--------------------------------------------------------------------------SELECT *  fromStudent S,sltcourse SCWHERES.stuid=Sc.stuidGO-------------------------------------------------------------------------- withReportcard as(SELECTS.stuid,s.name,sc. Coursename,sc. Score fromStudent S,sltcourse SCWHERES.stuid=sc.stuid)SELECT   Case  whenRowNum=1  ThenStuidELSE "' END  asID, Case  whenRowNum=1  ThenNameELSE "' END  asName, Coursename, score from(SELECT *, Row_number () Over(PARTITION byStuid,nameORDER  byScore) asRowNum fromReportcard) TBLGO--------------------------------------------------------------------------DROP TABLEStudentGODROP TABLESltcourseGO

Use Tempdbgo--------------------------------------------------------------------------IF object_id (' Student ', ' U ' ) is not nulldrop table Studentgocreate table Student (Stuid NVARCHAR (8) PRIMARY KEY, Name NVARCHAR (5), Entrancet IME DATETIME) Goinsert into Student (stuid,name,entrancetime) VALUES (' 20080001 ', ' Lily ', ' 2008-08-27 ') INSERT into Student (stuid,name,entrancetime) VALUES (' 20090002 ', ' Lucy ', ' 2009-08-26 ') INSERT into Student (Stuid,name, Entrancetime) VALUES (' 20070003 ', ' Jack ', ' 2007-08-28 ') GO--------------------------------------------------------------------------IF object_id (' sltcourse ', ' U ') is not Nulldrop table Sltcoursegocreate table Sltcourse--selectivecourse (ID INT PRIMARY KEY IDENTITY), Stuid Nvarc HAR (8), Coursename NVARCHAR (Ten), score INT) Goinsert into Sltcourse (stuid,coursename,score) VALUES (' 20070003 ', ' computer Repair ', ' + ' insert into Sltcourse (stuid,coursename,score) VALUES (' 20070003 ', ' paper-cut ', ') insert into Sltcourse (Stuid, Coursename,score) VALUES (' 20070003 ', ' CityField planning ', sltcourse) insert into Stuid,coursename,score VALUES (' 20070003 ', ' Information retrieval ', ' + ') insert into Sltcourse (Stuid, Coursename,score) VALUES (' 20080001 ', ' flower arrangement ', "Sltcourse") INSERT into the values (' 20080001 ', ' paper-cut ', ) insert into Sltcourse (' 20080001 ', ' embroidery ', stuid,coursename,score) insert into Sltcourse (Stuid,coursename, Score) VALUES (' 20090002 ', ' flower Arrangement ', 98) GO--------------------------------------------------------------------------SELECT * from Studentgoselect * Sltcoursego--------------------------------------------------------------------------SELECT * from Student S, Sltcourse SC WHERE s.stuid= Sc.stuidgo--------------------------------------------------------------------------with Reportcard as (SELECT S. Stuid,s.name,s.entrancetime,sc. Coursename,sc.         Score from Student S,sltcourse SC WHERE s.stuid=sc.stuid) The SELECT case is rownum=1 then Stuid ELSE ' END as ID, Case is Rownum=1 then name ELSE "END as Name, case is Rownum=1 then ENtrancetime ELSE ' END as Entrancetime, Coursename, Scorefrom (SELECT *,row_number () over (PARTITION by Stuid,name,entrancetime ORDER by score) as RowNum from Reportcard) Tblgo--------------------------------------------------------------------------DROP Table Studentgodrop table Sltcoursego

　　

With Reportcard as (SELECT s.stuid,s.name,s.entrancetime,sc. Coursename,sc. Score from Student S,sltcourse SC WHERE s.stuid=sc.stuid) SELECT case while  rownum=1 then stuid         ELSE ' END as id,< C2/>case when Rownum=1 then name            ELSE "END as Name, case is        rownum=1 then entrancetime    ELSE cast (Nullif (" , ') as DateTime) END as Entrancetime,        coursename,        scorefrom (SELECT *,row_number () over  (PARTITION by Stuid , Name,entrancetime ORDER by score) as RowNum from Reportcard) Tblgo

　　

SQL Server r2--row_number () removes duplicate content from the same column in the same row