sql-server - 多连接 Tsql 合并低效代码
问题描述
需要帮助使代码更高效。
我有以下选择（将成为更新语句），现在我必须有 8 个完全相同的数据连接。我尝试了一些事情并失败了，但也许这里的集体智慧会对如何提高效率有所了解。
我有以下表格
#temp_data
customer season id percent
1 2016 835 35.93000
1 2016 836 31.82000
1 2016 837 11.88000
1 2016 841 5.09000
1 2016 839 15.28000
1 2017 836 31.40000
1 2017 837 11.45000
1 2017 839 14.04000
1 2017 841 6.40000
1 2017 835 36.70000
和
lt_program_data
customer season value_835 value_836 value_837 value_838 value_839 value_840 value_841 value_842
1 2016 35.93000 31.82000 11.88000 NULL 15.28000 NULL 5.09000 NULL
1 2017 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2018 NULL NULL NULL NULL NULL NULL NULL NULL
1 2019 NULL NULL NULL NULL NULL NULL NULL NULL
正在发生的事情是，我们在季节/客户/id 值上匹配两个表。在#temp_data 表中不存在值的情况下，我们使用存在的最大值。我能够整理代码以使其正常工作。然而，它又大又丑，而且效率不高。
我要么必须有 8 个更新语句，其中我明确说明每个 id 值（835、836 等）。或者一个包含 8 个连接的大型语句实际上是同一件事。关于如何更新以下内容以提高效率的任何建议都会很棒。
正如您所看到的，下面实际上是一遍又一遍地重复完全相同的事情。虽然我了解选择部分可能需要 8 个单独的语句，但有没有办法将 8 个左外连接消除为一个。
代码：
select a.customer,
fyear,
value_835 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 835),
c1.[percent] ),
value_836 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 836),
c2.[percent] ),
value_837 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 837),
c3.[percent] ),
value_838 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 838),
c4.[percent] ),
value_839 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 839),
c5.[percent] ),
value_840 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 840),
c6.[percent] ),
value_841 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 841),
c7.[percent] ),
value_842 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 842),
c8.[percent] )
from #lt_program_data a
left outer join ( --835
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 835
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c1 on a.customer = c1.customer
left outer join ( --836
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 836
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c2 on a.customer = c2.customer
left outer join ( --837
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 837
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c3 on a.customer = c3.customer
left outer join ( --838
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 838
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c4 on a.customer = c4.customer
left outer join ( --839
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 839
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c5 on a.customer = c5.customer
left outer join ( --840
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 840
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c6 on a.customer = c6.customer
left outer join ( --841
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 841
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c7 on a.customer = c7.customer
left outer join ( --842
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 842
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c8 on a.customer = c8.customer
这是最后的示例输出。
customer fyear value_835 value_836 value_837 value_838 value_839 value_840 value_841 value_842
1 2016 35.93000 31.82000 11.88000 NULL 15.28000 NULL 5.09000 NULL
1 2017 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2018 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2019 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
解决方案
我发现将我的逻辑查询块包装在 CTE 中有助于我更清楚地思考它。这是pivots#temp_data 而不是unpivot-ing lt_program_data。如果我对您的理解正确，您应该能够使用 LatestData 来获得您需要的东西。left outer joincross apply
;with LatestData as (
select Customer, Season, [835],[836],[837],[838],[839],[840],[841],[842] from
(select Customer, Season, [percent], id from temp_data td) as TempSource
pivot
( sum([percent]) for id in ([835],[836],[837],[838],[839],[840],[841],[842])) as TempPivoted
)
select a.customer,
a.season,
value_835 = coalesce(a.value_835, latest.[835]),
value_836 = coalesce(a.value_836, latest.[836]),
value_837 = coalesce(a.value_837, latest.[837]),
value_838 = coalesce(a.value_838, latest.[838]),
value_839 = coalesce(a.value_839, latest.[839]),
value_840 = coalesce(a.value_840, latest.[840]),
value_841 = coalesce(a.value_841, latest.[841]),
value_842 = coalesce(a.value_842, latest.[842])
from lt_program_data a
cross apply (select top 1 * from LatestData where LatestData.customer = a.customer and latestdata.season <= a.season order by season desc) as latest
推荐阅读
- typescript - 为什么某些包的自动导入不起作用？
- java - 如何从java中的类对象列表中获取第一个匹配元素的索引（不使用任何第三方库）
- xsd - xml 模式：检查列表是否升序
- sql - 以与现有 id 字段相同的模式创建随机值
- javascript - 用两个组件反应 handleChange 错误
- c - 是否在有符号的固定宽度整数上定义和可移植按位运算？
- python - 在熊猫中添加一个带有当前年份的新列
- javascript - How can i overwrite data on HTML Datatables?
- python-3.x - 查找与嵌套字典中的值匹配的所有键，并以这些值作为键创建一个新字典
- firebase - 在 Xamarin.iOS 中为 Google 移动广告支持添加包会导致链接错误