以下是数据库内容,想查出一张卡一个月内的消费,充值总金额,用什么语句
卡号 类型 金额
00001 充值 500.00
00002 消费 100.00
00003 消费 150.00
00001 消费 50.00
00001 消费 100.00
00004 充值 500.00最后结果:
卡号 充值总金额 消费总金额
00001 500.00 150.00
00002 0.00 100.00
00003 0.00 150.00
00004 500.00 0.00
卡号 类型 金额
00001 充值 500.00
00002 消费 100.00
00003 消费 150.00
00001 消费 50.00
00001 消费 100.00
00004 充值 500.00最后结果:
卡号 充值总金额 消费总金额
00001 500.00 150.00
00002 0.00 100.00
00003 0.00 150.00
00004 500.00 0.00
select t1.卡号,t1.充值总金额,t2.消费总金额
from (select 卡号,sum(金额) 充值总金额 from tb where 类型='充值' group by 卡号) t1
left join (select 卡号,sum(金额) 消费总金额 from tb where 类型=' 消费' group by 卡号) t2
on t1.卡号=t2.卡号
go
create table #temp( [卡号] varchar(100), [类型] varchar(100), [金额] DECIMAL(10,2));
insert #temp
select '00001','充值','500.00' union all
select '00002','消费','100.00' union all
select '00003','消费','150.00' union all
select '00001','消费','50.00' union all
select '00001','消费','100.00' union all
select '00004','充值','500.00' --SQL:
SELECT [卡号],[充值总金额]=ISNULL([充值],0), [消费总金额]=ISNULL([消费],0) FROM
(
select [卡号], [类型], [金额]=SUM([金额]) from #temp
GROUP BY [卡号], [类型]
) a
PIVOT
(MAX([金额]) FOR [类型] IN([充值], [消费])) b
/*
卡号 充值总金额 消费总金额
00001 500.00 150.00
00002 0.00 100.00
00003 0.00 150.00
00004 500.00 0.00
*/
SELECT
[卡号],
[充值总金额] = ISNULL(SUM(CASE [类型] WHEN '充值' THEN [金额] END),0),
[充值总金额] = ISNULL(SUM(CASE [类型] WHEN '消费' THEN [金额] END),0)
from #temp
GROUP BY [卡号]
/*
卡号 充值总金额 充值总金额
00001 500.00 150.00
00002 0.00 100.00
00003 0.00 150.00
00004 500.00 0.00
*/
select [卡号], isnull([充值],0) as [充值总金额],isnull([消费],0) as [消费总金额] from
(
select t.[卡号],t.[类型],SUM(t.[金额]) as [金额] from #temp t
group by t.[卡号],t.[类型]
) a
pivot
(min([金额]) for [类型] in ([消费],[充值])) pvt
先把结果存入视图,然后再对视图进行查询create view vPntSum as
select cardno,
b.pnt_amt,b.per_amt
From
(
select cardno,ISNULL(PNT,0) as pnt_amt,ISNULL(PER,0) as per_amt from
(
select t.cardno,t.tx_code,SUM(t.req_txnamt) as req_txnamt from L2_pnt_per_sum t
group by t.[cardno],t.[tx_code]
) a
pivot
(min([req_txnamt]) for [tx_code] in([PER],[PNT])) pvt
) bselect max(cardno)
,sum(pnt_amt) as pnt_amt
,sum(per_amt) as per_amt
from [L2SettleDB].[dbo].[vPntSum]
--where cardno like '047238888900010607%'
group by substring(cardno,1,18);