有一消费表tab
code(代码) indate(输入日期) sl(数量) je(金额)内容如下
-- select * from tab order by indatecode indate sl je008 2007-01-01 1 10
008 2007-02-01 1 10
002 2007-03-01 1 10
003 2007-04-01 1 10
004 2007-05-01 1 10现在希望同编码的消费合并,输入日期取最后的一个日期,
消费列表按indate 排序,也就是达到下面的效果code indate sl je008 2007-02-01 2 20
002 2007-03-01 1 10
003 2007-04-01 1 10
004 2007-05-01 1 10请教这个sql如何改写,谢谢
code(代码) indate(输入日期) sl(数量) je(金额)内容如下
-- select * from tab order by indatecode indate sl je008 2007-01-01 1 10
008 2007-02-01 1 10
002 2007-03-01 1 10
003 2007-04-01 1 10
004 2007-05-01 1 10现在希望同编码的消费合并,输入日期取最后的一个日期,
消费列表按indate 排序,也就是达到下面的效果code indate sl je008 2007-02-01 2 20
002 2007-03-01 1 10
003 2007-04-01 1 10
004 2007-05-01 1 10请教这个sql如何改写,谢谢
select code, indate=max(indate), sl=sum(sl), je=sum(je)
from tab
group by code
insert tab select '008', '2007-01-01', 1, 10
union all select '008', '2007-02-01', 1, 10
union all select '002', '2007-03-01', 1, 10
union all select '003', '2007-04-01', 1, 10
union all select '004', '2007-05-01', 1, 10
select code, indate=min(indate), sl=sum(sl), je=sum(je)
from tab
group by code
order by indate --result
code indate sl je
---------- ------------------------------------------------------ ----------- -----------
008 2007-01-01 00:00:00.000 2 20
002 2007-03-01 00:00:00.000 1 10
003 2007-04-01 00:00:00.000 1 10
004 2007-05-01 00:00:00.000 1 10(4 row(s) affected)
insert tab select '008', '2007-01-01', 1, 10
union all select '008', '2007-02-01', 1, 10
union all select '002', '2007-03-01', 1, 10
union all select '003', '2007-04-01', 1, 10
union all select '004', '2007-05-01', 1, 10select code , convert(varchar(10),max(indate),120) as indate , sum(sl) as sl , sum(je) as je from tab group by code order by indate
drop table tab--结果
code indate sl je
---------- ---------- ----------- -----------
008 2007-02-01 2 20
002 2007-03-01 1 10
003 2007-04-01 1 10
004 2007-05-01 1 10