复杂的SQL查询

Question

我有下面的db表(为了说明这个问题而简化了)。

CampaignTx

campaignTx_id | member_id | date_created | shop_id
1 | 2 | 7/12/2009 | 2
2 | 4 | 7/13/2009 | 3
3 | 6 | 7/14/2009 | 4
4 | 5 | 8/14/2009 | 3
5 | 10| 8/19/2009 | 1

可靠性

Reliability_id | campaignTx_id | status
1 | 3 | 0
2 | 2 | 1
3 | 4 | 2
4 | 5 | 3
5 | 7 | 1

商店

Shop_id | Shop_name | City_id
1 | shop 1| 5
2 | shop 2| 7
3 | shop 3| 7
4 | shop 4| 6

城市

City_id | City_name
5 | city 1
6 | city 2
7 | city 3

我想要的是下表(每行按城市、年份和月份分组)：

City| year | month| num_of_campaignTx_records | num_of_reliability_records | num_of reliability_records with status = 0 |num_of reliability_records with status = 1| num_of reliability_records with status = 2| num_of reliability_records with status = 3

我应该如何编写SQL查询来获取这个表？

我现在有以下查询，但我不知道如何写最后4列：

select datepart(year,[Tx].date_created) as year,
datepart(month,[Tx].date_created) as month,
[city].nameTc as city,
count([Tx].date_created) as 'total num of campaign Tx records', 
count([rel].CreateDate) as 'num of reliability records'

from campaigntx as [Tx]

full join [Reliability] as [rel]
on [rel].[CampaignTx_id] = [Tx].[CampaignTx_id]

join shop as [shop]
on [Tx].shop_id = [shop].shop_id

join City as [city]
on [city].city_id = [shop].city_id

group by datepart(year,[Tx].date_created),datepart(month,[Tx].date_created), [city].nameTc

Answer 1

我还没有测试过它，但是下面几行中的内容应该可以工作

sum(when [rel].status = 1 then 1 else 0) as 'num of reliability records'

Answer 2

看起来像是PIVOT的案例

WITH Qry AS (
select datepart(year,[Tx].date_created) as year,
datepart(month,[Tx].date_created) as month,
[city].nameTc as city,
count([Tx].date_created) as 'total num of campaign Tx records', 
count([rel].CreateDate) as 'num of reliability records',
[re].status
from campaigntx as [Tx]

full join [Reliability] as [rel]
on [rel].[CampaignTx_id] = [Tx].[CampaignTx_id]

join shop as [shop]
on [Tx].shop_id = [shop].shop_id

join City as [city]
on [city].city_id = [shop].city_id

group by datepart(year,[Tx].date_created),datepart(month,[Tx].date_created), [city].nameTc
)
SELECT year, month, city, [toal num of campaign Tx records], 
  [0] as [No status 0], [1] as [No status 1], [2] as [No status 2], ...
 FROM Qry
PIVOT ( SUM([number or reliability records]) FOR status in ([0],[1],[2],...) ) AS PVT;