在SQL中转换差异表的多列

我创建了一些带有随机值的临时表，以说明:

create table #A
(
    Client int,
    Consumption1 int,
    Consumption2 int,
    Consumption3 int,
    Consumption4 int,
    Consumption5 int,
    Consumption6 int,
    Consumption7 int,
    Consumption8 int,
    Consumption9 int,
    Consumption10 int,
    Consumption11 int,
    Consumption12 int,
    Consumption13 int,
    Consumption14 int,
    Consumption15 int,
    Consumption16 int,
    Consumption17 int,
    Consumption18 int,
    Consumption19 int,
    Consumption20 int,
    Consumption21 int,
    Consumption22 int,
    Consumption23 int,
    Consumption24 int,
    Consumption25 int,
    Consumption26 int,
    Consumption27 int,
    Consumption28 int
)

create table #B
(
    Client int,
    Arrive1 int,
    Arrive2 int,
    Arrive3 int,
    Arrive4 int,
    Arrive5 int,
    Arrive6 int,
    Arrive7 int,
    Arrive8 int,
    Arrive9 int,
    Arrive10 int,
    Arrive11 int,
    Arrive12 int,
    Arrive13 int,
    Arrive14 int,
    Arrive15 int,
    Arrive16 int,
    Arrive17 int,
    Arrive18 int,
    Arrive19 int,
    Arrive20 int,
    Arrive21 int,
    Arrive22 int,
    Arrive23 int,
    Arrive24 int,
    Arrive25 int,
    Arrive26 int,
    Arrive27 int,
    Arrive28 int
)

create table #C
(
    Client int,
    Stock1 int,
    Stock2 int,
    Stock3 int,
    Stock4 int,
    Stock5 int,
    Stock6 int,
    Stock7 int,
    Stock8 int,
    Stock9 int,
    Stock10 int,
    Stock11 int,
    Stock12 int,
    Stock13 int,
    Stock14 int,
    Stock15 int,
    Stock16 int,
    Stock17 int,
    Stock18 int,
    Stock19 int,
    Stock20 int,
    Stock21 int,
    Stock22 int,
    Stock23 int,
    Stock24 int,
    Stock25 int,
    Stock26 int,
    Stock27 int,
    Stock28 int
)

insert into #A values
(4587, 1897163, 1750380, 5487230, 5086290, 3232280, 6014338, 5978823, 5864160, 8462496, 3945424, 6641780, 6268308, 924802, 9246014, 8089559, 6079526, 7332457, 4971714, 379325, 1651062, 5878994, 4987033, 9802665, 8163241, 180040, 3309288, 6966085, 6791869),
(1056, 3693126, 6085435, 2576225, 906414, 9308827, 7912118, 4690652, 2298023, 5333767, 1550576, 3324191, 8750357, 2135258, 3403408, 6807101, 1750717, 8367234, 5176016, 1008922, 9641689, 3564379, 5398274, 4521049, 5767916, 779819, 5466675, 4020480, 7245496),
(8977, 6496397, 7097447, 5026248, 3686617, 4583368, 4018366, 5718120, 318667, 2281474, 4044423, 9424092, 5355506, 7723824, 8635663, 7122301, 3401243, 2478769, 6540496, 8370652, 268827, 8858460, 4468893, 2184993, 416313, 5099858, 170593, 6609073, 7137023)

insert into #B values
(4587, 2667122, 2542268, 1118532, 2284504, 2147917, 1084679, 2729616, 1175793, 2649496, 1699428, 507390, 108714, 828544, 1358961, 1754942, 2084233, 1964319, 1751282, 2047706, 705322, 2918377, 1001676, 1494300, 2937679, 2216776, 2485884, 1217744, 366044),
(1056, 213746, 483120, 1689874, 2063834, 473576, 2063958, 2212633, 819822, 1569991, 1159024, 479248, 495937, 1923624, 1557142, 1164307, 2669944, 2161610, 1187291, 1083194, 1947235, 136211, 1749596, 692847, 1947817, 207300, 2784799, 2061996, 1248644),
(8977, 2085112, 2935995, 1977982, 633723, 2426369, 1156016, 1011041, 470338, 926361, 1244144, 625813, 945190, 1217648, 376607, 89393, 2944627, 1118893, 2939117, 213832, 2269515, 2261478, 1637021, 1961149, 1145781, 206511, 1563378, 1272576, 212847)

insert into #C values
(4587, 806, 797, 310, 784, 411, 629, 966, 134, 963, 313, 160, 136, 620, 690, 945, 649, 718, 207, 457, 125, 311, 911, 799, 629, 404, 510, 650, 793),
(1056, 599, 411, 843, 311, 752, 560, 797, 304, 805, 289, 476, 535, 315, 336, 805, 487, 820, 125, 589, 269, 415, 409, 329, 245, 316, 737, 561, 176),
(8977, 841, 583, 833, 372, 374, 776, 152, 676, 854, 404, 517, 571, 694, 315, 315, 116, 163, 948, 598, 681, 226, 691, 684, 372, 450, 772, 646, 581)

例如，如果您知道每个可能的Client值都会存在于消耗表中，则只需对字段求和并连接表即可:

select 
    con.Client,
    sto.Stock1 + sto.Stock2 + sto.Stock3 + sto.Stock4 + sto.Stock5 + sto.Stock6 + sto.Stock7 + sto.Stock8 + sto.Stock9 + sto.Stock10 + sto.Stock11 + sto.Stock12 + sto.Stock13 + sto.Stock14 + sto.Stock15 + sto.Stock16 + sto.Stock17 + sto.Stock18 + sto.Stock19 + sto.Stock20 + sto.Stock21 + sto.Stock22 + sto.Stock23 + sto.Stock24 + sto.Stock25 + sto.Stock26 + sto.Stock27 + sto.Stock28 as Stock,
    con.Consumption1 + con.Consumption2 + con.Consumption3 + con.Consumption4 + con.Consumption5 + con.Consumption6 + con.Consumption7 + con.Consumption8 + con.Consumption9 + con.Consumption10 + con.Consumption11 + con.Consumption12 + con.Consumption13 + con.Consumption14 + con.Consumption15 + con.Consumption16 + con.Consumption17 + con.Consumption18 + con.Consumption19 + con.Consumption20 + con.Consumption21 + con.Consumption22 + con.Consumption23 + con.Consumption24 + con.Consumption25 + con.Consumption26 + con.Consumption27 + con.Consumption28 as Consumption,
    arr.Arrive1 + arr.Arrive2 + arr.Arrive3 + arr.Arrive4 + arr.Arrive5 + arr.Arrive6 + arr.Arrive7 + arr.Arrive8 + arr.Arrive9 + arr.Arrive10 + arr.Arrive11 + arr.Arrive12 + arr.Arrive13 + arr.Arrive14 + arr.Arrive15 + arr.Arrive16 + arr.Arrive17 + arr.Arrive18 + arr.Arrive19 + arr.Arrive20 + arr.Arrive21 + arr.Arrive22 + arr.Arrive23 + arr.Arrive24 + arr.Arrive25 + arr.Arrive26 + arr.Arrive27 + arr.Arrive28 as Arrive
from #A con
left join #B arr on con.Client = arr.Client
left join #C sto on con.Client = sto.Client

如果一个客户端可能只存在于一个或两个表中，而您不知道是哪一个(S)，那么您需要首先区分客户端列表:

select
    cli.Client,
    sto.Stock1 + sto.Stock2 + sto.Stock3 + sto.Stock4 + sto.Stock5 + sto.Stock6 + sto.Stock7 + sto.Stock8 + sto.Stock9 + sto.Stock10 + sto.Stock11 + sto.Stock12 + sto.Stock13 + sto.Stock14 + sto.Stock15 + sto.Stock16 + sto.Stock17 + sto.Stock18 + sto.Stock19 + sto.Stock20 + sto.Stock21 + sto.Stock22 + sto.Stock23 + sto.Stock24 + sto.Stock25 + sto.Stock26 + sto.Stock27 + sto.Stock28 as Stock,
    con.Consumption1 + con.Consumption2 + con.Consumption3 + con.Consumption4 + con.Consumption5 + con.Consumption6 + con.Consumption7 + con.Consumption8 + con.Consumption9 + con.Consumption10 + con.Consumption11 + con.Consumption12 + con.Consumption13 + con.Consumption14 + con.Consumption15 + con.Consumption16 + con.Consumption17 + con.Consumption18 + con.Consumption19 + con.Consumption20 + con.Consumption21 + con.Consumption22 + con.Consumption23 + con.Consumption24 + con.Consumption25 + con.Consumption26 + con.Consumption27 + con.Consumption28 as Consumption,
    arr.Arrive1 + arr.Arrive2 + arr.Arrive3 + arr.Arrive4 + arr.Arrive5 + arr.Arrive6 + arr.Arrive7 + arr.Arrive8 + arr.Arrive9 + arr.Arrive10 + arr.Arrive11 + arr.Arrive12 + arr.Arrive13 + arr.Arrive14 + arr.Arrive15 + arr.Arrive16 + arr.Arrive17 + arr.Arrive18 + arr.Arrive19 + arr.Arrive20 + arr.Arrive21 + arr.Arrive22 + arr.Arrive23 + arr.Arrive24 + arr.Arrive25 + arr.Arrive26 + arr.Arrive27 + arr.Arrive28 as Arrive
from
(
    select con.Client from #A con
    union
    select arr.Client from #B arr
    union
    select sto.Client from #C sto
) cli
left join #A con on cli.Client = con.Client
left join #B arr on cli.Client = arr.Client
left join #C sto on cli.Client = sto.Client

然而，正如 comments 中指出的那样，这个表的设计似乎相当糟糕，可能应该更改，例如:

甚至:

在这种情况下，您的查询将变得简单得多:

select
    Client,
    sum(Stock) as Stock,
    sum(Consumption) as Consumption,
    sum(Arrive) as Arrive
from combined_table
group by Client