select DateKey as 时间维度,cast(df.dbmoney as decimal(18,2)) 营销费用,cast(dc.dbamount as decimal(18,2)) 合同金额
from Dim_Date d
left join fee df on d.DateKey = Convert(varchar(8),df.dDate,112)
left join [contract] dc on d.DateKey = Convert(varchar(8),dc.dsign,112)
where df.dbmoney is not null or dc.dbamount is not null
--MSSQL2008及以上版本:;with aaa as
(
select a.DateKey as [时间维度],
isnull(cast(b.dbmoney as decimal(18,2)),0) as [营销费用],
isnull(cast(c.dbamount as decimal(18,2)),0) as [合同金额]
from Dim_Date as a left join
fee as b on a.DateKey=convert(varchar(8),b.dDate,112) left join
contract as c on a.DateKey=convert(8).c.dsign,112)
)
select *
from aaa
where [营销费用]>0
or [合同金额]>0
order by [时间维度];
--NULL的话,可以使用ISNULL处理为0。
3 个回复
悟 - 取是能力,舍是境界 2015-10-26 回答
赞同来自:
gogodiy - 天善智能数据库专家、Tableau爱好者 2015-10-26 回答
赞同来自:
(
select a.DateKey as [时间维度],
isnull(cast(b.dbmoney as decimal(18,2)),0) as [营销费用],
isnull(cast(c.dbamount as decimal(18,2)),0) as [合同金额]
from Dim_Date as a left join
fee as b on a.DateKey=convert(varchar(8),b.dDate,112) left join
contract as c on a.DateKey=convert(8).c.dsign,112)
)
select *
from aaa
where [营销费用]>0
or [合同金额]>0
order by [时间维度];
--NULL的话,可以使用ISNULL处理为0。
天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2015-10-26 回答
赞同来自:
要求结果: 合并数据通过日期表
思路: 先用表A INNER JOIN 日期表, 再用 表A去 FULL OUTER JOIN 表B
得到的数据类似:
结果里DateKey列并未合并, 需要在 SELECT 列的时候,加以判断。
CASE WHEN 如果 表A.DateKey ISNULL 那么用 表B.DateKey
否则继续表A.DateKey
关于为何要用FULL JOIN,注意看 表A和表B数据的数据。 再去复习一下 LEFT RIGHT FULL JOIN