有一张表,里面有一个值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要怎么写??谢谢!!!
(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
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 datediff(dd,n.tradeDay ,m.tradeDay) = 1然后判断拿最大的日期及最大的值出来
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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
) 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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
order by Amount desc
ID Amount TradeDate
1 1000 2011-03-01
2 2000 2011-03-01
3 3000 2011-03-02
4 4000 2011-03-03
5 5000 2011-03-03那现在2011-03-02的数据就是1000+2000+3000=6000
2011-03-03的数据就是3000+4000+5000=12000
那么最值最大的日期是2011-03-03,对应的值是12000
当然,这只是一个片段,一份数据里面会有这个月的全部日期(当然里面会有2011-02-28来求2011-03-01的值)
应该举这样的数据:
ID Amount TradeDate
1 1010 2011-03-01
2 2300 2011-03-01
3 3004 2011-03-02
4 4170 2011-03-03
5 5062 2011-03-03
insert into tb values(1 ,1000 ,'2011-03-01')
insert into tb values(2 ,2000 ,'2011-03-01')
insert into tb values(3 ,3000 ,'2011-03-02')
insert into tb values(4 ,4000 ,'2011-03-03')
insert into tb values(5 ,5000 ,'2011-03-03')
goselect 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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
/*
tradeDay Amount
---------- -----------
2011-03-01 3000
2011-03-02 6000
2011-03-03 12000(所影响的行数为 3 行)
*/--然后判断拿最大的日期及最大的值出来
select max(tradeDay) tradeDay, max(Amount) 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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
) t
/*
tradeDay Amount
---------- -----------
2011-03-03 12000(所影响的行数为 1 行)
*/--最大的日期所在的记录
select top 1 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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
order by m.tradeDay desc
/*
tradeDay Amount
---------- -----------
2011-03-03 12000(所影响的行数为 1 行)
*/--最大的值出来所在的记录
select top 1 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 datediff(dd,n.tradeDay ,m.tradeDay) = 1
order by Amount desc
/*
tradeDay Amount
---------- -----------
2011-03-03 12000(所影响的行数为 1 行)
*/drop table tb