表A
id ,tradetime(交易日期),trademoney(交易金额)
我想取出这样的列
ID ,lasttime(最后交易日期) ,allmoney(总交易金额) monthmoney(本月交易金额) 怎么写
id ,tradetime(交易日期),trademoney(交易金额)
我想取出这样的列
ID ,lasttime(最后交易日期) ,allmoney(总交易金额) monthmoney(本月交易金额) 怎么写
解决方案 »
- “NP-0001”“NP-0002”“NP-0003”“NP-0004”....这样的自动编号格式在字段上如何设置?[50分,明白后立即结]
- 请DAWUGUI 潇洒老乌龟(爱新觉罗.毓华)帮忙解决!!!
- 提问:如何将WEB SERVER端的EXCEL,导入到数据库SERVER端?大家有什么好办法?
- 插入记录后,弹出新窗口的问题.
- 请大家帮忙看一下~~~~~~~~~~~~~```
- 用ADO.net连接数据库出错,error26,急求!
- 请教一简单的存储过程问题
- 怎樣得到表中不一樣的記錄(至少有一個字段的值不同)﹐
- 我的sql server服务器无法开启,连想在管理那里查看它的属性都不行。请问各位知道是什么原因造成的吗?谢谢!
- 谁能帮我解决服务器--客户端数据传送问题?
- 数据备份后 清空表
- 百分求助,急,up有分
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 Sum(交易金额) from 表名 Where 表名.ID=a.ID AND month(交易日期)=month(getdate()) Group BY ID)
From 表名 a Group By ID
[本月交易金额]=Sum(交易金额)
From 表名 a Group By ID,month(交易日期)
[最后交易日期]=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
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',1000select lasttime,(select sum(trademoney) from trad)allmoney,monthmoney
from(select max(tradetime) lasttime,sum(trademoney)'monthmoney'
from trad group by Month(tradetime)) a
select sum(b.交易金额) from 表 b where b.交易日期 between 月初 and 月末 as 本月交易金额
frm 表 Group By id
select lasttime,(select sum(trademoney) from trad)allmoney,monthmoney
from(select max(tradetime) lasttime,sum(trademoney)'monthmoney'
from trad where Month(tradetime)=Month(getdate())
group by Month(tradetime))a
select max(tradetime)'lasttime',(select sum(trademoney) from trad)'allmoney',sum(trademoney)'monthmoney'
from trad where Month(tradetime)=Month(getdate())
select max(tradetime)'lasttime',(select sum(trademoney) from trad)'allmoney',sum(trademoney)'monthmoney'
from trad where CONVERT(varchar(6), tradetime,112)=CONVERT(varchar(6), getdate(),112)