SQL Server 2008 R2——ROW_NUMBER() 去掉不同行中相同列的重复内容
2023-09-14 09:00:19 时间
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
相关文章
- SQL Server中,Numric,Decimal,Money三种字段类型的区别
- windows redis:Uncaught exception 'RedisException' with message 'Redis server went away'
- SQL Server基础Sql语句复习
- SQL Server实现 LeetCode 176 第二高的薪水
- SQL Server账号密码(sa)登录失败 错误原因:18456
- 虚拟机备份克隆导致SQL SERVER 出现IO错误案例
- SQL Server查询时添加一列连续的自增列
- SQL Server 2008 R2——ROW_NUMBER() 去掉不同行中相同列的重复内容
- [SQL] sql server中如何查看执行效率不高的语句
- mock server相关解决方案
- Oracle与Sql Server复制表结构及数据
- oracle与sql server大批量存储过程可以替换部分
- SQL Server vs Oracle 简单语法比较
- 快速迁移数据中心:华为云数据库SQL Server实践案例技术解析
- SQL Server 变量定义
- sql server查看表大小
- SQL——Sql_Server中如何判断表中某字段、判断表、判断存储过程以及判断函数是否存在
- 利用PowerUpSQL攻击SQL Server实例——本质上就是利用弱密码登录SQL server
- SQL Server-【知识与实战V】视图
- Ambari-server 架构