| 大神们！！！！！！怎么能把卡类类型，如图（金卡）合并以及相关的积分，积分损失金额，费用等数字合并。 这是查询语句declare @开始时间__datetime datetime,@结束时间__datetime datetime,@供应商编码 nvarchar(20),@卡类型 nvarchar(20),@机构 nvarchar(20) set @开始时间__datetime='${sdate}' set @结束时间__datetime='${edate}' set @供应商编码='${gys}' set @卡类型='' set @机构='${jg}' select a.c_amount as c_amount ,DATEPART(yy,a.c_dt)*100 + DATEPART(mm,a.c_dt) AS mm,a.c_fee_class , case when a.c_fee_name='白金卡损失'then '白金卡' when a.c_fee_name='金卡损失' then '金卡' else '天美卡' end as c_cardname, case when len(a.c_id)=14 then SUBSTRING(a.c_id,3,5) else SUBSTRING(a.c_id,4,5) end as c_provider into #a from tb_o_feeg a(nolock) where EXISTS(SELECT 1 FROM tb_o_fee(nolock) WHERE c_id=a.c_id AND c_status='已审核' ) and a.c_fee_class = '会员积分损失' and ( @开始时间__datetime is null or datediff(day,@开始时间__datetime,a.c_dt )>=0) and ( @结束时间__datetime is null or datediff(day,@结束时间__datetime,a.c_dt)<=0) and (@供应商编码 is null or @供应商编码='' or case when len(a.c_id)=14 then SUBSTRING(a.c_id,3,5) else SUBSTRING(a.c_id,4,5) end=@供应商编码) AND (@卡类型 is null or @卡类型='' OR CHARINDEX(@卡类型,a.c_fee_name)=0 ) SELECT SUM(c_amount) AS c_amount ,mm,c_cardname, c_provider INTO #b FROM #a GROUP BY mm,c_cardname, c_provider SELECT a.c_store_id as 机构,a.c_provider as 供应商编码, b.c_name as 供应商名称, a.c_cardname as 卡类型, DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime) mm, SUM(a.c_amount_jf_sg) AS 积分, cast(ROUND(SUM(a.c_amount_jf_sg)*0.01,2) as decimal(9,2)) AS 积分损失金额 INTO #c from tb_provider_score_fee a(nolock) join tb_partner b(nolock) on a.c_provider = b.c_no WHERE ( @供应商编码 is null or @供应商编码='' or a.c_provider=@供应商编码) and ( @卡类型 is null or @卡类型='' or a.c_cardname=@卡类型) and (@开始时间__datetime is null or datediff(day,@开始时间__datetime,a.c_datetime)>=0) and (@结束时间__datetime is null or datediff(day,@结束时间__datetime,a.c_datetime)<=0) GROUP BY a.c_store_id,a.c_provider,a.c_cardname,b.c_name,DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime) order by a.c_store_id,a.c_provider,DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime),a.c_cardname /*----++++*/ SELECT #c.机构 as 机构,#c.供应商编码 as 供应商编码, #c.供应商名称 as 供应商名称, #c.卡类型 as 卡类型 , #c.mm AS 日期, cast(#c.积分 as decimal(9,2) ) as 积分, #c.积分损失金额 as 积分损失金额, isnull(#b.c_amount,0) as 费用, cast(#c.积分损失金额-isnull(#b.c_amount,0) as decimal(9,2) ) as 差额 FROM #c LEFT JOIN #b ON #c.供应商编码= #b.c_provider AND #c.卡类型=#b.c_cardname AND #c.mm=#b.mm where ( @机构 is null or @机构='8' or #c.机构=@机构) ORDER BY #c.机构,#c.供应商编码,#c.mm,#c.卡类型 编辑于 2017-7-10 15:46 |
