日期:2014-05-18  浏览次数:20666 次

求教一条SQL
有一张表,里面有一个值Amount,和一个tradeDay(包含一个月的日期,例如:2011-3月,里面就会有2011-2-28~2011-3-31之间的数据)。现在求每个日期对应的值(比如要求3月5号这个日期对应的值,那就是3月5号的数据+上3月4号的数据之和就是3月5号的数据,其中3月4号或3月5号可以有多条数据,我们要对其汇总Amount),就这样求出这个月各个日期的值,然后判断拿最大的日期及最大的值出来)
请问各位大牛这样的SQL要怎么写??谢谢!!!

------解决方案--------------------
给个典型的例子记录,才好理解

先按日期分组汇总
再加上前一天的数值
------解决方案--------------------
SQL code
建议你提供详细的资料:
例如表的结构,表之间的关系,测试数据,相关算法及需要的结果。
这样有助于我们理解你的意思,更主要的是能尽快让你获得答案或解决问题的方法。

------解决方案--------------------
SQL code
select m.tradeDay , m.Amount + isnull(n.Amount,0) from
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) m
left join
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) n
on m.tradeDay = n.tradeDay

然后判断拿最大的日期及最大的值出来
select max(tradeDay) , max(Amount) from
(
select m.tradeDay , m.Amount + isnull(n.Amount,0) Amount from
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) m
left join
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) n
on m.tradeDay = n.tradeDay
) t

最大的日期所在的记录
select m.tradeDay , m.Amount + isnull(n.Amount,0) Amount from
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) m
left join
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) n
on m.tradeDay = n.tradeDay
order by m.tradeDay desc

最大的值出来所在的记录
select m.tradeDay , m.Amount + isnull(n.Amount,0) from
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) m
left join
(select convert(varchar(10),tradeDay,120) tradeDay, sum(Amount) Amount from tb group by convert(varchar(10),tradeDay,120)) n
on m.tradeDay = n.tradeDay
order by Amount desc