请问怎么把两个分组查询表拼接在一起，并且进行运算？请教各路大神！

Question

第一个查询

SELECT
	[询盘表].[来源], 
	sum(case when [询盘表].[询盘日期]>= '2021-05-01'  and [询盘表].[询盘日期] <= '2021-05-31' then 1 else 0 end) AS [询盘], 
	sum(case when [询盘表].[询盘日期]>= '2021-05-01'  and [询盘表].[询盘日期] <= '2021-05-31' and [询盘表].[有效否] = '有' then 1 else 0 end) AS [有效], 
	sum(case when [询盘表].[上门日期]>= '2021-05-01'  and [询盘表].[上门日期] <= '2021-05-31' then 1 else 0 end) AS [上门], 
	sum(case when [询盘表].[交定日期]>= '2021-05-01'  and [询盘表].[交定日期] <= '2021-05-31' then 1 else 0 end) AS [交定], 
	sum(case when [询盘表].[施工日期]>= '2021-05-01'  and [询盘表].[施工日期] <= '2021-05-31' then 1 else 0 end) AS [施工]
FROM
	dbo.[询盘表]
GROUP BY
	[询盘表].[来源]

 

 

第二个查询

SELECT
	[株洲消费表].[来源], 
	sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[消费] else 0 end) as '消费', 
	sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[点击] else 0 end) as '点击', 
	sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[展现] else 0 end) as '展现'
FROM
	dbo.[株洲消费表]
GROUP BY
	[株洲消费表].[来源]

 

 

怎么按来源拼接在一起，并且每个列之间可以进行算数运算等到下面的表：

Answer 1

  

select a.*,b.消费,b.点击,b.展现
from (
	SELECT
		[询盘表].[来源], 
		sum(case when [询盘表].[询盘日期]>= '2021-05-01'  and [询盘表].[询盘日期] <= '2021-05-31' then 1 else 0 end) AS [询盘], 
		sum(case when [询盘表].[询盘日期]>= '2021-05-01'  and [询盘表].[询盘日期] <= '2021-05-31' and [询盘表].[有效否] = '有' then 1 else 0 end) AS [有效], 
		sum(case when [询盘表].[上门日期]>= '2021-05-01'  and [询盘表].[上门日期] <= '2021-05-31' then 1 else 0 end) AS [上门], 
		sum(case when [询盘表].[交定日期]>= '2021-05-01'  and [询盘表].[交定日期] <= '2021-05-31' then 1 else 0 end) AS [交定], 
		sum(case when [询盘表].[施工日期]>= '2021-05-01'  and [询盘表].[施工日期] <= '2021-05-31' then 1 else 0 end) AS [施工]
	FROM
		dbo.[询盘表]
	GROUP BY
		[询盘表].[来源]
) a
left join (
	SELECT
		[株洲消费表].[来源], 
		sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[消费] else 0 end) as '消费', 
		sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[点击] else 0 end) as '点击', 
		sum(case when [株洲消费表].[日期]>= '2021-05-01'  and [株洲消费表].[日期] <= '2021-05-31' then [株洲消费表].[展现] else 0 end) as '展现'
	FROM
		dbo.[株洲消费表]
	GROUP BY
		[株洲消费表].[来源]
) b on a.来源=b.来源

这个是不同表之间的数据，你可以用来源进行关联查询