sql - Join 和 Pivot 语句 SQL Server

Question

我想旋转并加入以从 3 个表中进行选择

表 1:INT、VARCHAR、FLOAT

ID Name  value
---------------------------
1   a1  32116580
2   a2  50785384
3   a3  54327508
4   a4  61030844

表 2:INT、VARCHAR、FLOAT

ID   Name     value
---------------------------
1   x11   61326085092
2   x12   80368184260
3   x13   83023398776
4   x14   91144307692
5   x22   95486535484
6   x23   90357090612
7   x24   100588807668
8   x33   707811916752
9   x34   93128452928
10  x44   84566653668

表 3:INT、VARCHAR、FLOAT

ID   Name     value
---------------------------
1   q1   61326085092
2   q2   95486535484
3   q3   707811916752
4   q4   84566653668

输出表:

column1              column2            column3             column4
--------------------------------------------------------------------------
  a1*a1/(q1+q1+x11)  a1*a2/(q1+q2+x12)    a1*a3/(q1+q3+x13)     a1*a4/(q1+q4+x14)
  null               a2*a2/(q2+q2+x22)    a2*a3/(q2+q3+x23)     a2*a4/(q2+q4+x24)
  null               null                 a3*a3/(q3+q3+x339     a3*a4/(q3+q4+x34)
  null               null                 null                  a4*a4/(q4+q4+x44)

(我将 3 个不同表的列的“名称”而不是数字)

• 如何做到这一点？
• 我想我必须做两个支点？和取消透视？...

好了不知道怎么完成吧..

SELECT *
  FROM (
        SELECT
         t1.a1,
         t1.a2,
         t2.x,
         t3.q
         FROM table1 t1
         INNER JOIN table2 t2
         ON t1.id = t2.id
         ...
   ) Output
   PIVOT (
      name IN (
            ...
      PIVOT(name ... )
      )
  ) PivotTable

更新以前我有 * 的我已将其更改为除法和求和，* 的 只是一个例子，

Answer 1

示例表

create table Table1(ID int, Name varchar(10), value float)
insert table1 select
1 ,'a1', 32116580 union all select
2 ,'a2', 50785384 union all select
3 ,'a3', 54327508 union all select
4 ,'a4', 61030844

create table Table2(ID int, Name varchar(10), value float)
insert Table2 select
1 ,'x11', 61326085092 union all select
2 ,'x12', 80368184260 union all select
3 ,'x13', 83023398776 union all select
4 ,'x14', 91144307692 union all select
5 ,'x22', 95486535484 union all select
6 ,'x23', 90357090612 union all select
7 ,'x24', 100588807668 union all select
8 ,'x33', 707811916752 union all select
9 ,'x34', 93128452928 union all select
10 ,'x44', 84566653668

create table Table3(ID int, Name varchar(10), value float)
insert Table3 select
1 ,'q1', 61326085092 union all select
2 ,'q2', 95486535484 union all select
3 ,'q3', 707811916752 union all select
4 ,'q4', 84566653668

您需要的查询，对于 N = 4。对于任何其他 N，只需使用动态 SQL 构建查询，更改 ** 所示所需的 2 行。

;with coords(i,row,col,total,N) as (
select 1,1,1,N.N*(N.N+1)/2, N.N
from (select count(*) N from table1) N
union all
select i+1,
       case when col+1>N then row+1 else row end,
       case when col+1>N then row+1 else col+1 end,
       total, N
from coords
where i<total
)
select [1],[2],[3],[4] -- **, e.g. ,[5],[6],etc
from
(
    select
        c.row,
        c.col,
        cellvalue= ar.value*ac.value/(qr.value+qc.value+x.value)
    from coords c
    inner join table1 ar on ar.id = c.row
    inner join table1 ac on ac.id = c.col
    inner join table3 qr on qr.id = c.row
    inner join table3 qc on qc.ID = c.col
    inner join table2 x on x.ID = c.i
) p
pivot (max(cellvalue) for col in ([1],[2],[3],[4])) pv   -- **
order by row

输出:

1                      2                      3                      4
---------------------- ---------------------- ---------------------- ----------------------
5606.50338459295       6876.83326310711       2047.51559459649       8269.17991568225
NULL                   9003.55641750708       3087.36780924588       11044.2303130135
NULL                   NULL                   1389.95405212248       3744.35614651666
NULL                   NULL                   NULL                   14681.7678040306

动态版

declare @Sql nvarchar(max)
select @Sql = ISNULL(@sql + ',', '') + QUOTENAME(RIGHT(number,10))
from master..spt_values
where type='P' and number between 1 and (select COUNT(*) From table1)
set @Sql = '
;with coords(i,row,col,total,N) as (
select 1,1,1,N.N*(N.N+1)/2, N.N
from (select count(*) N from table1) N
union all
select i+1,
       case when col+1>N then row+1 else row end,
       case when col+1>N then row+1 else col+1 end,
       total, N
from coords
where i<total
)
select ' + @sql + '
from
(
    select
        c.row,
        c.col,
        cellvalue= ar.value*ac.value/(qr.value+qc.value+x.value)
    from coords c
    inner join table1 ar on ar.id = c.row
    inner join table1 ac on ac.id = c.col
    inner join table3 qr on qr.id = c.row
    inner join table3 qc on qc.ID = c.col
    inner join table2 x on x.ID = c.i
) p
pivot (max(cellvalue) for col in (' + @sql + ')) pv
order by row
option (maxrecursion 0)  -- ! use with caution
'
exec(@sql)