SQL Server 实用语句

 

创建临时表 #Test

?

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE #Test( ID INT, Name VARCHAR(50) ) INSERT INTO #Test( ID, Name ) VALUES ( 1, 'A' ) INSERT INTO #Test( ID, Name ) VALUES ( 2, 'A' ) INSERT INTO #Test( ID, Name ) VALUES ( 4, 'A' ) INSERT INTO #Test( ID, Name ) VALUES ( 5, 'B' ) INSERT INTO #Test( ID, Name ) VALUES ( 8, 'B' ) INSERT INTO #Test( ID, Name ) VALUES ( 10, 'B' ) INSERT INTO #Test( ID, Name ) VALUES ( 3, 'C' ) INSERT INTO #Test( ID, Name ) VALUES ( 6, 'D' ) INSERT INTO #Test( ID, Name ) VALUES ( 7, 'D' ) GO

 

删除/过滤 重复记录

?

1 2	-- （推荐） 将 #Test 中 name 重复的记录删除，只保留 ID 最小的记录 DELETE t1 FROM #Test t1 WHERE t1.ID > (SELECT MIN(ID) FROM #Test t2 WHERE t1.Name = t2.Name)

也可以用 ROW_NUMBER()，但是这个效果不行，而且难以理解

?

1 2 3 4

-- 使用 ROW_NUMBER() 过滤重复记录 SELECT * FROM (SELECT ID, Name, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) row_index FROM #Test ) t WHERE t.row_index = 1

　　

字符串分割

select value as col from STRING_SPLIT('test1,test2,test3', ',')

 

 行转列

?

1 2 3

SELECT r.userid ,STUFF((SELECT ',' + CONVERT(VARCHAR(12),DepartmentID) FROM tMemberDepartment WHERE userid = r.userid AND ISNULL(deleteflag,0)=0 FOR XML PATH('')) ,1,1,'') department FROM dbo.tMember r

　　

PIVOT  和 UNPIVOT 关键字

reference：勤奋的耕牛 的 SQL Server中行列转换 Pivot UnPivot

PIVOT 行转列

?

1 2 3 4 5 6 7 8 9 10

CREATE table #tb(姓名 varchar(10),课程 varchar(10),分数 int) INSERT INTO #tb values('张三','语文',74) INSERT INTO #tb values('张三','数学',83) INSERT INTO #tb values('张三','物理',93) INSERT INTO #tb values('李四','语文',74) INSERT INTO #tb values('李四','数学',84) INSERT INTO #tb values('李四','物理',94) GO //行转列 SELECT * FROM #tb PIVOT (MAX(分数) FOR 课程 IN (语文,数学,物理)) a

　　

UNPIVOT 列转行

?

1 2 3 4 5 6 7 8

CREATE TABLE #tb2(姓名 varchar(10),语文 int,数学 int,物理 int) INSERT INTO #tb2 values('张三',74,83,93) INSERT INTO #tb2 values('李四',74,84,94) GO //列转行 SELECT 姓名,课程, SUM(分数) 分数 FROM #tb2 UNPIVOT (分数 FOR  课程 IN ([语文],[数学],[物理])) t