是这样的 需要查询出一张表的数据 并且是2011-01-01的 然后首先不能重复 其次是该表中有一列积分 由于有了这一列所以DISTINCT的话 还是会把所有重复的数据查出来 一个account会有很多行 其它列的信息都是一样的 就是积分不同 现在需要知道account最后的积分剩余了多少 *****8交易序号最大的一定是对应最后的余额****** 请高人来写个SQL 表格式如下 并且得出的结果必须是唯一的和账号匹配的 还有 时间必须要指定 因为其中还有2011-02-01等等...account date jifen code(交易序号)
jsaaa 2011-01-01 21 1
jsaaa 2011-01-01 33 2
jsaaa 2011-01-01 12 3
david 2011-01-01 55 1
david 2011-01-01 130 2
david 2011-01-01 11 3
ecri 2011-01-01 200 1
ecri 2011-01-01 300 2
ecri 2011-01-01 260 3希望得到的结果
jsaaa 2011-01-01 12
david 2011-01-01 11
ecri 2011-01-01 260
jsaaa 2011-01-01 21 1
jsaaa 2011-01-01 33 2
jsaaa 2011-01-01 12 3
david 2011-01-01 55 1
david 2011-01-01 130 2
david 2011-01-01 11 3
ecri 2011-01-01 200 1
ecri 2011-01-01 300 2
ecri 2011-01-01 260 3希望得到的结果
jsaaa 2011-01-01 12
david 2011-01-01 11
ecri 2011-01-01 260
FROM (SELECT account ,date ,jifen
ROW_NUMBER () OVER (PARTITION BY account ORDER BY jifen asc)
rn
FROM tb)
WHERE rn = 1
create table jifen_table(account char(20),date date,jifen smallint,code smallint);
insert into jifen_table values('jsaaa','2011-01-01',21,1);
insert into jifen_table values('jsaaa','2011-01-01',33,2);
insert into jifen_table values('jsaaa','2011-01-01',12,3);insert into jifen_table values('david','2011-01-01',55,1);
insert into jifen_table values('david','2011-01-01',130,2);
insert into jifen_table values('david','2011-01-01',11,3);insert into jifen_table values('ecri','2011-01-01',200,1);
insert into jifen_table values('ecri','2011-01-01',300,2);
insert into jifen_table values('ecri','2011-01-01',260,3);select jifen_table.account,jifen_table.date,jifen_table.jifen from (select max(code) max_code ,account from jifen_table group by account) tmp ,jifen_table where jifen_table.code=max_code and tmp.account=jifen_table.account and jifen_table.date='2011-01-01';
在oracle中:
create table jifen_table(account char(20),date_ date,jifen smallint,code smallint);
insert into jifen_table values('jsaaa',to_date('2011-01-01','YYYY-MM-DD'),21,1);
insert into jifen_table values('jsaaa',to_date('2011-01-01','YYYY-MM-DD'),33,2);
insert into jifen_table values('jsaaa',to_date('2011-01-01','YYYY-MM-DD'),12,3);insert into jifen_table values('david',to_date('2011-01-01','YYYY-MM-DD'),55,1);
insert into jifen_table values('david',to_date('2011-01-01','YYYY-MM-DD'),130,2);
insert into jifen_table values('david',to_date('2011-01-01','YYYY-MM-DD'),11,3);insert into jifen_table values('ecri',to_date('2011-01-01','YYYY-MM-DD'),200,1);
insert into jifen_table values('ecri',to_date('2011-01-01','YYYY-MM-DD'),300,2);
insert into jifen_table values('ecri',to_date('2011-01-01','YYYY-MM-DD'),260,3);
select jifen_table.account,to_char(jifen_table.date_,'YYYY-MM-DD') as date_,jifen_table.jifen from (select max(code) max_code ,account from jifen_table group by account) tmp ,jifen_table where jifen_table.code=max_code and tmp.account=jifen_table.account and jifen_table.date_=to_date('2011-01-01','YYYY-MM-DD');
SELECT account ,date ,jifen
FROM (SELECT account ,date ,jifen,
ROW_NUMBER () OVER (PARTITION BY account ORDER BY code asc) rn
FROM TABLE)
WHERE rn = 1
(select account,date,jifen row_number()over(partition by account order by code desc) as rn from 表明 where date=参数) t
where t.rn=1