提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  后端

当前栏目

SQL Server 2008 R2——ROW_NUMBER() 去掉不同行中相同列的重复内容

serverSQL number 内容 不同 重复 去掉 相同
2023-09-14 08:58:57 时间 

USE tempdb
GO
--------------------------------------------------------------------------
IF OBJECT_ID('Student','U') IS NOT NULL
DROP TABLE Student
GO

CREATE TABLE Student
(
    StuID NVARCHAR(8) PRIMARY KEY,
    Name NVARCHAR(5) 
)
GO

INSERT INTO Student(StuID,Name) VALUES('20080001','Lily')
INSERT INTO Student(StuID,Name) VALUES('20080002','Lucy')
INSERT INTO Student(StuID,Name) VALUES('20080003','Jack')
GO
--------------------------------------------------------------------------
IF OBJECT_ID('SltCourse','U') IS NOT NULL
DROP TABLE SltCourse
GO

CREATE TABLE SltCourse    --SelectiveCourse
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    StuID NVARCHAR(8),
    CourseName NVARCHAR(10),
    Score    INT 
)
GO

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','电脑维修',90)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','剪纸',80)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','市场策划',95)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','信息检索',100)

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080002','插花',98)
GO
--------------------------------------------------------------------------
SELECT * FROM Student
GO
SELECT * FROM SltCourse
GO
--------------------------------------------------------------------------
SELECT * FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
GO
--------------------------------------------------------------------------
WITH ReportCard 
AS(
SELECT s.StuID,s.Name,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
)
SELECT  CASE WHEN RowNum=1 THEN StuID   ELSE '' END AS ID,
        CASE WHEN RowNum=1 THEN Name    ELSE '' END AS Name,
        CourseName,
        Score
FROM (SELECT *,ROW_NUMBER()  OVER (PARTITION BY StuID,Name ORDER BY Score) AS RowNum FROM ReportCard) TBL
GO
--------------------------------------------------------------------------
DROP TABLE Student
GO
DROP TABLE SltCourse
GO

 

 

USE tempdb
GO
--------------------------------------------------------------------------
IF OBJECT_ID('Student','U') IS NOT NULL
DROP TABLE Student
GO

CREATE TABLE Student
(
    StuID NVARCHAR(8) PRIMARY KEY,
    Name NVARCHAR(5),
    EntranceTime DATETIME
)
GO

INSERT 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 NULL
DROP TABLE SltCourse
GO

CREATE TABLE SltCourse    --SelectiveCourse
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    StuID NVARCHAR(8),
    CourseName NVARCHAR(10),
    Score    INT 
)
GO

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','电脑维修',90)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','剪纸',80)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','市场策划',95)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','信息检索',100)

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)

INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20090002','插花',98)
GO
--------------------------------------------------------------------------
SELECT * FROM Student
GO
SELECT * FROM SltCourse
GO
--------------------------------------------------------------------------
SELECT * FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
GO
--------------------------------------------------------------------------
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 WHEN RowNum=1 THEN StuID         　ELSE '' END AS ID,
        CASE WHEN RowNum=1 THEN Name            ELSE '' END AS Name,
        CASE WHEN RowNum=1 THEN EntranceTime    ELSE '' END AS EntranceTime,
        CourseName,
        Score
FROM (SELECT *,ROW_NUMBER()  OVER (PARTITION BY StuID,Name,EntranceTime ORDER BY Score) AS RowNum FROM ReportCard) TBL
GO
--------------------------------------------------------------------------
DROP TABLE Student
GO
DROP TABLE SltCourse
GO

　　

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 WHEN RowNum=1 THEN StuID         　ELSE '' END AS ID,
        CASE WHEN RowNum=1 THEN Name            ELSE '' END AS Name,
        CASE WHEN RowNum=1 THEN EntranceTime    ELSE cast(nullif('','') as datetime) END AS EntranceTime,
        CourseName,
        Score
FROM (SELECT *,ROW_NUMBER()  OVER (PARTITION BY StuID,Name,EntranceTime ORDER BY Score) AS RowNum FROM ReportCard) TBL
GO

　　

 

相关文章