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

SQL query to fetch output using table variables amp; join(使用表变量获取输出的 SQL 查询加入)
本文介绍了使用表变量获取输出的 SQL 查询加入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我正在编写一个 SQL 查询来获取一个表的数据,并使用多个或嵌套 SQL 语句或使用表变量将其与更多数据连接起来,然后再使用连接.

I am writing a SQL query to fetch the data of one table and join it with some more data either using multiple or nested SQL statements or using table variable and then use join later.

条件是,如果Spends Table中的Medium列是TV,那么在Output TableTV_Spends 应显示电视的 Spends 和该特定市场牌.这同样适用于 Medium Print.

The condition is that if the Medium column in Spends Table is TV then in the Output Table the TV_Spends should show the Spends of TV and that particular market & Brand. And this same is for Medium Print.

另外一个条件是,如果Spends Table中的Type列是CWB,那么该Primary_Brand的Spendscode> 和 Medium 不应用于 TV_SpendsPrint_Spends 计算.

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_KeyTV_SpendsPrint_Spends 的总和市场

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

支出表

<头>
Primary_Brand_Key市场类型花费
Kornet电视英国NULL1000
Kornet电视波兰NULL2000
Kornet打印波兰NULL3000
Kornet打印NULLCWB7000
Tamas电视英国NULL9000

预期产出表

<头>
Primary_Brand市场TV_SpendsPrint_SpendsTotal_Spends
Kornet英国1000NULL1000
Kornet波兰200030005000
Tamas英国9000NULL9000

输出即将到来

<头>
Primary_Brand市场TV_SpendsPrint_SpendsTotal_Spends
NULLNULLNULLNULL1000
NULLNULLNULLNULL5000
NULLNULLNULLNULL9000
NULLNULLNULLNULLNULL
NULLNULLNULL3000NULL
NULLNULLNULLNULLNULL
NULLNULL1000NULLNULL
NULLNULL2000NULLNULL
NULLNULL9000NULLNULL
NULL英国NULLNULLNULL
NULL波兰NULLNULLNULL
NULL英国NULLNULLNULL
KornetNULLNULLNULLNULL
KornetNULLNULLNULLNULL
TamasNULLNULLNULLNULL

我编写的 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' 仅用于 tvprint 列.但是,如果您真的想要所有三个都使用它,请改用 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 查询加入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)