問(wèn)題描述
我正在編寫一個(gè) SQL 查詢來(lái)獲取一個(gè)表的數(shù)據(jù)，并使用多個(gè)或嵌套 SQL 語(yǔ)句或使用表變量將其與更多數(shù)據(jù)連接起來(lái)，然后再使用連接.
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 應(yīng)顯示電視的 Spends 和該特定市場(chǎng)牌.這同樣適用于 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.
另外一個(gè)條件是，如果Spends Table中的Type列是CWB，那么該Primary_Brand的Spendscode> 和 Medium 不應(yīng)用于 TV_Spends 和 Print_Spends 計(jì)算.
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 的總和市場(chǎng)
Total_Spends is sum of TV_Spends and Print_Spends for that Brand_Key & Market
支出表
| Primary_Brand_Key | 中 | 市場(chǎng) | 類型 | 花費(fèi) |
|---|---|---|---|---|
| Kornet | 電視 | 英國(guó) | NULL | 1000 |
| Kornet | 電視 | 波蘭 | NULL | 2000 |
| Kornet | 打印 | 波蘭 | NULL | 3000 |
| Kornet | 打印 | NULL | CWB | 7000 |
| Tamas | 電視 | 英國(guó) | NULL | 9000 |
預(yù)期產(chǎn)出表
| Primary_Brand | 市場(chǎng) | TV_Spends | Print_Spends | Total_Spends |
|---|---|---|---|---|
| Kornet | 英國(guó) | 1000 | NULL | 1000 |
| Kornet | 波蘭 | 2000 | 3000 | 5000 |
| Tamas | 英國(guó) | 9000 | NULL | 9000 |
輸出即將到來(lái)
| Primary_Brand | 市場(chǎng) | TV_Spends | Print_Spends | Total_Spends |
|---|---|---|---|---|
| NULL | NULL | NULL | NULL | 1000 |
| NULL | NULL | NULL | NULL | 5000 |
| NULL | NULL | NULL | NULL | 9000 |
| NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 3000 | NULL |
| NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | 1000 | NULL | NULL |
| NULL | NULL | 2000 | NULL | NULL |
| NULL | NULL | 9000 | NULL | NULL |
| NULL | 英國(guó) | NULL | NULL | NULL |
| NULL | 波蘭 | NULL | NULL | NULL |
| NULL | 英國(guó) | NULL | NULL | NULL |
| Kornet | NULL | NULL | NULL | NULL |
| Kornet | NULL | NULL | NULL | NULL |
| Tamas | NULL | NULL | NULL | NULL |
我編寫的 SQL 查詢給出了 Output Coming 輸出，但是輸出應(yīng)該是 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
推薦答案
如果我關(guān)注你想要的，你可以使用條件聚合:
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;
您的問(wèn)題表明 'CWB' 僅用于 tv 和 print 列.但是，如果您真的想要所有三個(gè)都使用它，請(qǐng)改用 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.
這篇關(guān)于使用表變量獲取輸出的 SQL 查詢加入的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！