T-SQL笔记

纯粹工作笔记：

1.order by 通过多列取值的结果

order by name,age 表示的是当满足了所有name的排序之后，如果有name有重复的，就会按照age再去进行排序

2. join

示例数据

inner join

left join

right join

3.return

注：存储过程中as前面的是形参，as后面的是临时变量，用于存储过程

4.output

5.行转列

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

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

--行转列1

SELECT 姓名,

 max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end)语文,

 max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)数学,

 max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)物理

FROM tb

GROUP BY 姓名

--行转列2

SELECT  * FROM tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a;

--行转列3

DECLARE @sql VARCHAR(8000)

SET @sql=''  --初始化变量@sql

SELECT @sql=@sql+','+课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql=stuff(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)

--行转列4

DECLARE @sql2 VARCHAR(8000)

SELECT @sql2=isnull(@sql2+',','')+课程 FROM tb GROUP BY 课程           

SET @sql2='select * from tb pivot (max(分数) for 课程 in ('+@sql2+'))a'

exec(@sql2)

6.列转行

IF OBJECT_ID('tb')IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb

go

--列转行1
SELECT * FROM

(

 SELECT 姓名,课程='语文',分数=语文 FROM tb

 UNION ALL

 SELECT 姓名,课程='数学',分数=数学 FROM tb

 UNION ALL

 SELECT 姓名,课程='物理',分数=物理 FROM tb

) t

ORDER BY 姓名,CASE 课程 WHEN '语文' THEN 1 WHEN '数学' THEN 2 WHEN '物理' THEN 3 end

--行转列2

DECLARE @sql VARCHAR(8000)

SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='

+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb'

FROM syscolumns

WHERE Name!='姓名' AND ID=object_id('tb')--表名tb，不包含列名为姓名的其他列

ORDER BY colid

exec(@sql+' order by 姓名')

go

--行转列3

SELECT 姓名,课程,分数 FROM tb unpivot (分数 FOR 课程 IN([语文],[数学],[物理])) t

--行转列4

DECLARE @sql NVARCHAR(4000)

SELECT @sql=isnull(@sql+',','')+quotename(Name)

FROM syscolumns

WHERE ID=object_id('tb')AND Name NOT IN('姓名')

ORDER BY Colid

SET @sql='select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b'

exec(@sql)

7.行转列逻辑

其实根据#temp中的内容，select已经执行了2次查询，并且结果保存在了@sql中，最后的select无非是把结果取了出来，并且去掉了前面的逗号