从联盟中选择案例
问题描述:
为了报告和创建报告(MS SQL SERVER 2014)的目的,该报告将比较上周同一天和上个月同一天的交易数量和金额数量,我需要一些像这样的查询,这是不工作:从联盟中选择案例
Select
Acquirer = case when Date = @Day then a.Acquirer
when Date = @LastWeekDay then b.Acquirer
when Date = @LastMonthDay then c.Acquirer
end
, RC = case when Date = @Day then a.RC
when Date = @LastWeekDay then b.RC
when Date = @LastMonthDay then c.RC
end
, Desc = case when Date = @Day then a.Desc
when Date = @LastWeekDay then b.Desc
when Date = @LastMonthDay then c.Desc
end
, Day_Tr_Count = case when Date = @Day then a.Count end
, LWD_Tr_Count = case when Date = @LastWeekDay then b.Count end
, LMD_Tr_Count = case when Date = @LastMonthDay then c.Count end
, Day_Tr_Amount = case when Date = @Day then a.Amount end
, LWD_Tr_Amount = case when Date = @LastWeekDay then b.Amount end
, LMD_Tr_Amount = case when Date = @LastMonthDay then c.Amount end
from
(select * from T1
union
select * from T2
union
select * from T3) x
group by Acquirer,RC,Desc
所有3台拥有漂亮的结构相同:
T1,T2,T3(acquirer,RC,Desc,Count,Amount,Date).
报告应该是:
result_table(Acquirer,RC,Desc,Daily_Count,Last_Week_Day_Count,Last_Month_Count,Daily_Amount,Last_Week_Day_Amount,Last_Month_Day_Amount)
答
嗯。您有一个没有group by
的聚合查询。也许:
Select v.Acquirer, v.RC, v.Desc,
Day_Tr_Count = sum(case when Date = @Day then a.Count end),
LWD_Tr_Count = sum(case when Date = @LastWeekDay then b.Count end),
LMD_Tr_Count = sum(case when Date = @LastMonthDay then c.Count end),
Day_Tr_Amount = sum(case when Date = @Day then a.Amount end
LWD_Tr_Amount = sum(case when Date = @LastWeekDay then b.Amount end),
LMD_Tr_Amount = sum(case when Date = @LastMonthDay then c.Amount end)
from (select * from T1
union all
select * from T2
union all
select * from T3
) x cross apply
(values (case when Date = @Day then a.Acquirer
when Date = @LastWeekDay then b.Acquirer
when Date = @LastMonthDay then c.Acquirer
end,
case when Date = @Day then a.RC
when Date = @LastWeekDay then b.RC
when Date = @LastMonthDay then c.RC
end,
case when Date = @Day then a.Desc
when Date = @LastWeekDay then b.Desc
when Date = @LastMonthDay then c.Desc
end
) v (Acquirer, RC, Desc)
group by v.Acquirer, v.RC, v.Desc;
注:desc
是一列(或任何其他)的名声。这是一个SQL关键字(想想order by
)。
第1步:您可能需要'UNION ALL'。 – jarlh
它是如何*不工作*? –
一般的GROUP BY规则说:“如果指定了GROUP BY子句,则SELECT列表中的每个列引用都必须标识分组列或作为set函数的参数。” – jarlh