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

2021-09-17 09:55:46

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

==================================声明==================================

本文原创，转载在正文中显要的注明作者和出处，并保证文章的完整性。

未经作者同意请勿修改（包括本声明），保留法律追究的权利。

未经作者同意请勿用于学术性引用。

未经作者同意请勿用于商业出版、商业印刷、商业引用。

本文不定期修正完善，为保证内容正确，建议移步原文处阅读。

本文链接：http://www.cnblogs.com/wlsandwho/p/4416222.html

=======================================================================

总是写一些“战5渣”的东西，这次来个“战6渣”的。不然没法混了。

=======================================================================

举个例子，不一定好。

原创例子，必然没有雷同。

这个栗子果然坏了。

=======================================================================

将弄成这个样子。

=======================================================================

 1 USE tempdb
 2 GO
 3 --------------------------------------------------------------------------
 4 IF OBJECT_ID('Student','U') IS NOT NULL
 5 DROP TABLE Student
 6 GO
 7 
 8 CREATE TABLE Student
 9 (
10     StuID NVARCHAR(8) PRIMARY KEY,
11     Name NVARCHAR(5) 
12 )
13 GO
14 
15 INSERT INTO Student(StuID,Name) VALUES('20080001','Lily')
16 INSERT INTO Student(StuID,Name) VALUES('20080002','Lucy')
17 INSERT INTO Student(StuID,Name) VALUES('20080003','Jack')
18 GO
19 --------------------------------------------------------------------------
20 IF OBJECT_ID('SltCourse','U') IS NOT NULL
21 DROP TABLE SltCourse
22 GO
23 
24 CREATE TABLE SltCourse    --SelectiveCourse
25 (
26     ID INT PRIMARY KEY IDENTITY(1,1),
27     StuID NVARCHAR(8),
28     CourseName NVARCHAR(10),
29     Score    INT 
30 )
31 GO
32 
33 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','电脑维修',90)
34 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','剪纸',80)
35 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','市场策划',95)
36 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080003','信息检索',100)
37 
38 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
39 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
40 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)
41 
42 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080002','插花',98)
43 GO
44 --------------------------------------------------------------------------
45 SELECT * FROM Student
46 GO
47 SELECT * FROM SltCourse
48 GO
49 --------------------------------------------------------------------------
50 SELECT * FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
51 GO
52 --------------------------------------------------------------------------
53 WITH ReportCard 
54 AS(
55 SELECT s.StuID,s.Name,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
56 )
57 SELECT  CASE WHEN RowNum=1 THEN StuID   ELSE '' END AS ID,
58         CASE WHEN RowNum=1 THEN Name    ELSE '' END AS Name,
59         CourseName,
60         Score
61 FROM (SELECT *,ROW_NUMBER()  OVER (PARTITION BY StuID,Name ORDER BY Score) AS RowNum FROM ReportCard) TBL
62 GO
63 --------------------------------------------------------------------------
64 DROP TABLE Student
65 GO
66 DROP TABLE SltCourse
67 GO

=======================================================================

然而……（然而……）×N

假如说，有一个字段是DateTime类型，那么可能会出现类似“1900-01-01 00:00:00.000”的东西。

例如下面略微修改过的代码，添加了入学时间（EntranceTime）：

 1 USE tempdb
 2 GO
 3 --------------------------------------------------------------------------
 4 IF OBJECT_ID('Student','U') IS NOT NULL
 5 DROP TABLE Student
 6 GO
 7 
 8 CREATE TABLE Student
 9 (
10     StuID NVARCHAR(8) PRIMARY KEY,
11     Name NVARCHAR(5),
12     EntranceTime DATETIME
13 )
14 GO
15 
16 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20080001','Lily','2008-08-27')
17 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20090002','Lucy','2009-08-26')
18 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20070003','Jack','2007-08-28')
19 GO
20 --------------------------------------------------------------------------
21 IF OBJECT_ID('SltCourse','U') IS NOT NULL
22 DROP TABLE SltCourse
23 GO
24 
25 CREATE TABLE SltCourse    --SelectiveCourse
26 (
27     ID INT PRIMARY KEY IDENTITY(1,1),
28     StuID NVARCHAR(8),
29     CourseName NVARCHAR(10),
30     Score    INT 
31 )
32 GO
33 
34 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','电脑维修',90)
35 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','剪纸',80)
36 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','市场策划',95)
37 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','信息检索',100)
38 
39 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
40 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
41 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)
42 
43 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20090002','插花',98)
44 GO
45 --------------------------------------------------------------------------
46 SELECT * FROM Student
47 GO
48 SELECT * FROM SltCourse
49 GO
50 --------------------------------------------------------------------------
51 SELECT * FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
52 GO
53 --------------------------------------------------------------------------
54 WITH ReportCard 
55 AS(
56 SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
57 )
58 SELECT  CASE WHEN RowNum=1 THEN StuID         　ELSE '' END AS ID,
59         CASE WHEN RowNum=1 THEN Name            ELSE '' END AS Name,
60         CASE WHEN RowNum=1 THEN EntranceTime    ELSE '' END AS EntranceTime,
61         CourseName,
62         Score
63 FROM (SELECT *,ROW_NUMBER()  OVER (PARTITION BY StuID,Name,EntranceTime ORDER BY Score) AS RowNum FROM ReportCard) TBL
64 GO
65 --------------------------------------------------------------------------
66 DROP TABLE Student
67 GO
68 DROP TABLE SltCourse
69 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

则效果为

=======================================================================

但是怎么才能把变成呢？

码农公寓

相关文章