sql server中的表运算符

sql server中表运算符有join、apply、pivot和unpivot
1、join的逻辑处理顺序
a、交叉连接左输入和右输入
b、应用on子句筛选
c、添加外部行(外连接才有)
2、apply的逻辑处理顺序
a、把右表表达式应用到左表输入行
b、添加外部行（只有outer apply包含此步骤）
SELECT * from dbo.s
CROSS apply ( SELECT s1 +1 s2 ) as sd ;
把s中的每一列增加1

3、pivot 行转列
a、分组
b、隔离值
c、聚合
学生成绩表
create table dbo.student_score
(

id INT,
subject VARCHAR(2),
score DECIMAL(5,2)
)
INSERT into dbo.student_score VALUES
(1,2,10),
(1,3,30),
(1,4,40),
(2,2,50),
(2,3,60),
(2,4,70),
(3,2,10)
利用sql语句
SELECT
id,
SUM(case when subject =’2’ THEN score ELSE NULL END) ‘2’,
SUM(case when subject =’3’ THEN score ELSE NULL END) ‘3’,
SUM(case when subject =’4’ THEN score ELSE NULL END) ‘4’
FROM dbo.student_score
GROUP BY id

SELECT
id,[2], [3], [4]
FROM
(
SELECT
id,subject,score
FROM
dbo.student_score
) AS D
PIVOT (SUM (score) FOR subject IN ( [2], [3],[4] ))As p; 这里非常规标识符作为列名加[]

4、unpivot 列转行
a、生成副本
b、提取元素
c、删除无关交叉
利用sql语句
SELECT * FROM (
SELECT id,subject_name,
CASE WHEN subject_name =’yw’ THEN yw
WHEN subject_name =’sx’ THEN sx
WHEN subject_name =’yy’ THEN yy END as score
FROM dbo.student_subject_score
CROSS JOIN (VALUES(‘yw’),(‘sx’),(‘yy’)) subject(subject_name)) s
WHERE score is not NULL

SELECT id,score,subject_name
FROM dbo.student_subject_score
UNPIVOT (score FOR subject_name in (yw,sx,yy)) as u