急,求一存储过程 统计
表A
id ,tradetime(交易日期),trademoney(交易金额)
我想取出这样的列
ID ,lasttime(最后交易日期) ,allmoney(总交易金额) monthmoney(本月交易金额) 怎么写
[解决办法]
SELECT
ID,
MAX(tradetime) as lasttime,
SUM(trademoney) as allmoney,
SUM(CASE DATEDIFF(MM,tradetime,GETDATE())
WHEN 0 THEN trademoney
ELSE 0
END) as monthmoney
FROM
表A
GROUP BY
ID
[解决办法]
Select ID,[最后交易日期]=max(交易日期),[总交易金额]=sum(交易金额),
[本月交易金额]=(Select Sum(交易金额) from 表名 Where 表名.ID=a.ID AND month(交易日期)=month(getdate()) Group BY ID)
From 表名 a Group By ID
[解决办法]
SELECT ID,
[最后交易日期]=MAX(tradetime) ,
[总交易金额]=SUM(trademoney),
[本月交易金额]= sum(case when convert(varchar(7),tradetime,120)=convert(varchar(7),getdate(),120)
then trademoney else 0 end)
FROM table1
GROUP BY
ID
[解决办法]
动不动就proc
create table trad(id int identity(1,1) ,tradetime datetime,trademoney int)
insert into trad select '2007-01-30 ',1000
union all select '2007-1-30 ',1000
union all select '2007-1-31 ',1000
union all select '2007-1-30 ',1040
union all select '2007-1-31 ',1200
union all select '2007-1-31 ',1000
union all select '2007-1-30 ',1900
union all select '2007-1-31 ',1000
union all select '2007-2-1 ',1000
union all select '2007-2-12 ',1200
union all select '2007-2-20 ',1000
union all select '2007-2-10 ',1000
union all select '2007-2-28 ',1000
select lasttime,(select sum(trademoney) from trad)allmoney,monthmoney
from(select max(tradetime) lasttime,sum(trademoney) 'monthmoney '
from trad group by Month(tradetime)) a
[解决办法]
select id,max交易日期 '最后交易日期 ',sum交易金额 '总交易金额 ',
select sum(b.交易金额) from 表 b where b.交易日期 between 月初 and 月末 as 本月交易金额
frm 表 Group By id
