使用表变量获取输出的 SQL 查询加入

Also, another condition is that if the Type column in Spends Table is CWB then the Spends of that Primary_Brand and Medium should NOT be used in TV_Spends and Print_Spends calculation.

Total_Spends 是该 Brand_Key 的 TV_Spends 和 Print_Spends 的总和市场

Total_Spends is sum of TV_Spends and Print_Spends for that Brand_Key & Market

支出表

预期产出表

输出即将到来

我编写的 SQL 查询给出了 Output Coming 输出，但是输出应该是 Expected Output Table:-

SQL Query I have written which is giving the Output Coming output, However the Output should be Expected Output Table:-

declare @output_table_spends table
(
primary_brand_var nvarchar(255),
market_var nvarchar(255),
tv_spends decimal(11,2),
print_spends decimal(11,2)
total_spends_var  decimal(11,2)
)

Insert into @output_table_spends (primary_brand_var)
select Primary_Brand_Key from
dbo.Spends

Insert into @output_table_spends (market_var)
select Market from
dbo.Spends

Insert into @output_table_spends (tv_spends)
select sum(Amount_Spent_INR) 
  from dbo.Spends
  where medium='TV'
  group by Market
  
  Insert into @output_table_spends (print_spends)
select sum(Amount_Spent_INR) 
  from dbo.Spends
  where medium='Print'
  group by Market
  
  Insert into @output_table_spends (total_spends_var)
select sum(tv_spends,print_spends) 
  from dbo.Spends
  group by Market
  
  select * from dbo.Spends
  
  select distinct A.Primary_Brand_Key, A.Market, 
  B.tv_spends, B.print_spends, B.total_spends_var 
   from dbo.Spends A
  inner join @output_table_spends B
  on A.Primary_Brand_Key=B.primary_brand_var
  group by A.Primary_Brand_Key, A.Market, B.tv_spends, B.print_spends, B.total_spends_var

推荐答案

如果我关注你想要的，你可以使用条件聚合:

If I'm following what you want, you can use conditional aggregation:

select primary_brand_key, market,
       sum(case when medium = 'TV' and type <> 'CWB' then spends else 0 end) as tv,
       sum(case when medium = 'print' and type <> 'CWB' then spends else 0 end) as print,
       sum(spends)
from spends s
group by primary_brand_key, market;

您的问题表明 'CWB' 仅用于 tv 和 print 列.但是，如果您真的想要所有三个都使用它，请改用 where 子句.

Your question suggests that the 'CWB' is only needed for the tv and print columns. However, if you really want it for all three, then use a where clause instead.

这篇关于使用表变量获取输出的 SQL 查询加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！