SQL语句 自连表查询。inner join用法，partition by ，列转行查询

use mydb1
go
-- 表T_Employee2
-- Id  Name  Position  Dept
-- 1   张三   员工     市场部
-- 2   李四   经理     销售部
-- 3   王五   经理     市场部
-- 4   马六   员工     销售部
-- 5   钱七   员工     市场部
select * from T_Employee2

-- 需求：查询表中所有员工以及所在部门的经理姓名。（使用表自连接查询）

-- 分析：要求返回结果
-- Name  Position  Dept  经理
-- 张三  员工    市场部  王五
-- 马六  员工    销售部  李四
-- 钱七  员工    市场部  王五

-- 1、查出 每个部门对应的经理姓名
select Dept,Name from T_Employee2 where Position='经理'
-- 结果：
-- Dept    Name
-- 销售部  李四
-- 市场部  王五

-- 2、查出 所有的员工
select * from T_Employee2 where Position='员工'
-- 结果：
-- Id  Name  Position  Dept
-- 1   张三   员工     市场部
-- 4   马六   员工     销售部
-- 5   钱七   员工     市场部

--3、上面2个表连接查询，把1个表当做2个表来查询。
select t1.Name,Position,t1.Dept,t2.Name '经理' from 
(select * from T_Employee2 where Position='员工') as t1
inner join (select Dept,Name from T_Employee2 where Position='经理')as t2 on t1.Dept = t2.Dept

--4、简化后结果
select t1.Name,Position,t1.Dept,t2.Name '经理' from T_Employee2
as t1
inner join (select Dept,Name from T_Employee2 where Position='经理')as t2 on t1.Dept = t2.Dept
where t1.Position='员工'

-- 结果：
-- Name  Position  Dept  经理
-- 张三  员工    市场部  王五
-- 马六  员工    销售部  李四
-- 钱七  员工    市场部  王五

记录开窗函数分组查询

--自连表查询
select t.A,t.B,t.C,t.D from T_D as t
inner join (select B,row_number() over(order by MIN(A)) as E from T_D group by B) tt on t.B=tt.B
order by tt.E

select * from T_D order by min(A) over(partition by B)

下面是列转行

go
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
create table #temp(fnum int, fa int, fb int)
insert into #temp values
(15070, 1, 3),
(15070, 2, 0),
(15070, 3, 3),
(15070, 4, 1),
(15070, 5, 0),
(15070, 7, 1),
(15070, 8, 1),
(15070, 9, 1),
(15070, 10, 0),

(15071, 1, 3),
(15071, 2, 0),
(15071, 3, 1),
(15071, 4, 3),
(15071, 5, 0),
(15071, 7, 3),
(15071, 8, 1),
(15071, 9, 3),
(15071, 10, 3),

(15072, 1, 3),
(15072, 2, 3),
(15072, 3, 0),
(15072, 4, 1),
(15072, 5, 0), 
(15072, 7, 1),
(15072, 8, 0),
(15072, 9, 0),
(15072, 10, 0)
go
--select * from #temp 
--请求转化成：
--fnum       1  2  3  4  5  7  8  9 10
--15070 3   0   3  1  0 1  1  1   0
--15071    3   0   1  3  0 3  1   3  3
--15072    3   3   0  1  0  1 0   0  0 

select * from (select distinct fnum from #temp) as A
OUTER APPLY 
(select [fbs]= replace(replace(
  (select fb as value FROM #temp as B 
    where fnum = A.fnum order by B.fa FOR XML AUTO
  ),'<B value="',''),'"/>',' ')
)B


--创建新表
go
if OBJECT_ID('tempdb..#new') is not null
drop table #new
create table #new(fnum int,l_1 int,l_2 int,l_3 int,l_4 int,l_5 int,l_7 int,l_8 int,l_9 int,l_10 int)

--循环导入数据
declare @i int = 1;
declare @count int = (select count(*) from (select distinct fnum from #temp) as a);
declare @fnum varchar(50),@fbs varchar(50),@sql varchar(100);

while @i<=@count
begin

select @fnum=fnum, @fbs=fbs from
(select fnum,fbs,ROW_NUMBER() over(order by fnum) as row from (select distinct fnum from #temp) as A
OUTER APPLY 
(select [fbs]= replace(replace(
  (select fb as value FROM #temp as B 
    where fnum = A.fnum order by B.fa FOR XML AUTO
  ),'<B value="',''),'"/>',' ')
)B)tmp
where tmp.row = @i;

 set @sql='insert into #new values('+@fnum+',';
 set @sql=@sql + replace(@fbs,' ',',')+')';
 set @sql=replace(@sql,',)',')');
 exec (@sql) --执行SQL
 --PRINT @sql

set @i=@i+1
end

go

select * from #new

select fnum,
max(case fa when 1 then fb end) F1, 
max(case fa when 2 then fb end) F2,
max(case fa when 3 then fb end) F3,
max(case fa when 4 then fb end) F4,
max(case fa when 5 then fb end) F5,
max(case fa when 7 then fb end) F7,
max(case fa when 8 then fb end) F8,
max(case fa when 9 then fb end) F9,
max(case fa when 10 then fb end) F10 
from #temp group by fnum