表:
id name time number
1 aa 2009-10-1 11:20:15 1000
2 bb 2009-10-1 10:10:15 60
3 aa 2009-10-1 10:01:15 800
4 bb 2009-10-1 9:10:15 40
5 cc 2009-10-1 9:20:15 500
6 cc 2009-10-1 7:20:15 400
7 aa 2009-11-1 11:20:15 1000
8 bb 2009-11-1 10:10:15 60
9 aa 2009-11-1 10:01:15 800
10 bb 2009-11-1 9:10:15 40
11 cc 2009-11-1 9:20:15 500
12 cc 2009-11-1 7:20:15 400
数据库中的数据如上:
现在我想抽出来的东西,是按name不同,和日期不同,每天最后一天记录抽出来
如上我要得到的结果为:
name time number
aa 2009-10-1 11:20:15 1000
bb 2009-10-1 10:10:15 60
cc 2009-10-1 9:20:15 500
aa 2009-11-1 11:20:15 1000
bb 2009-11-1 10:10:15 60
cc 2009-11-1 9:20:15 500
请高手解答,谢谢。
id name time number
1 aa 2009-10-1 11:20:15 1000
2 bb 2009-10-1 10:10:15 60
3 aa 2009-10-1 10:01:15 800
4 bb 2009-10-1 9:10:15 40
5 cc 2009-10-1 9:20:15 500
6 cc 2009-10-1 7:20:15 400
7 aa 2009-11-1 11:20:15 1000
8 bb 2009-11-1 10:10:15 60
9 aa 2009-11-1 10:01:15 800
10 bb 2009-11-1 9:10:15 40
11 cc 2009-11-1 9:20:15 500
12 cc 2009-11-1 7:20:15 400
数据库中的数据如上:
现在我想抽出来的东西,是按name不同,和日期不同,每天最后一天记录抽出来
如上我要得到的结果为:
name time number
aa 2009-10-1 11:20:15 1000
bb 2009-10-1 10:10:15 60
cc 2009-10-1 9:20:15 500
aa 2009-11-1 11:20:15 1000
bb 2009-11-1 10:10:15 60
cc 2009-11-1 9:20:15 500
请高手解答,谢谢。
where not exists(select 1 from tablename t1
where t1.name = t.name
and trunc(t1.time) = trunc(t.time)
and t1.time > t.time);
select * from (select name,time,number, row_number() over(partition by name, trunc(time, 'D') order by time desc) rn from table_a) a where a.rn = 1
insert into ta values(1 , 'aa',to_date('2009-10-1 11:20:15','yyyy-mm-dd hh24:mi:ss'), 1000);
insert into ta values(2 , 'bb',to_date('2009-10-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(3 , 'aa',to_date('2009-10-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(4 , 'bb',to_date('2009-10-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40 );
insert into ta values(5 , 'cc',to_date('2009-10-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(6 , 'cc',to_date('2009-10-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400 );
insert into ta values(7 , 'aa',to_date('2009-11-1 11:20:15','yyyy-mm-dd hh24:mi:ss'),1000 );
insert into ta values(8 , 'bb',to_date('2009-11-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(9 , 'aa',to_date('2009-11-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(10, 'bb',to_date('2009-11-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40);
insert into ta values(11, 'cc',to_date('2009-11-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(12, 'cc',to_date('2009-11-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400);select id,name,tm,num
from(
select id,name,tm,num,row_number() over(partition by trunc(tm),name order by tm desc) rn
from ta)
where rn=1
汗,trunc好像在oracle和sqlserver中是不同的,标准sql不太好办了
INSERT INTO test_tb(id, name, datetime, number1) values(2,'bb',to_date('2009-10-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
INSERT INTO test_tb(id, name, datetime, number1) values(3,'aa',to_date('2009-10-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
INSERT INTO test_tb(id, name, datetime, number1) values(4,'bb',to_date('2009-10-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40);
INSERT INTO test_tb(id, name, datetime, number1) values(5,'cc',to_date('2009-10-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
INSERT INTO test_tb(id, name, datetime, number1) values(6,'cc',to_date('2009-10-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400);
INSERT INTO test_tb(id, name, datetime, number1) values(7,'aa',to_date('2009-11-1 11:20:15','yyyy-mm-dd hh24:mi:ss'),1000);
INSERT INTO test_tb(id, name, datetime, number1) values(8,'bb',to_date('2009-11-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
INSERT INTO test_tb(id, name, datetime, number1) values(9,'aa',to_date('2009-11-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
INSERT INTO test_tb(id, name, datetime, number1) values(10,'bb',to_date('2009-11-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40);
INSERT INTO test_tb(id, name, datetime, number1) values(11,'cc',to_date('2009-11-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
INSERT INTO test_tb(id, name, datetime, number1) values(12,'cc',to_date('2009-11-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400); SELECT t1.name,
TO_CHAR(t1.datetime,'yyyy-mm-dd hh24:mi:ss') AS "DATE",
t1.number1
FROM test_tb t1
WHERE t1.name IN (
SELECT t2.name
FROM test_tb t2 group by t2.name, to_char(t2.datetime,'yyyy-mm-dd') )
AND TO_CHAR(t1.datetime,'yyyy-mm-dd hh24:mi:ss') IN (
SELECT TO_CHAR(max(t2.datetime),'yyyy-mm-dd hh24:mi:ss')
FROM test_tb t2 group by t2.name, to_char(t2.datetime,'yyyy-mm-dd') )
ORDER BY TO_CHAR(t1.datetime,'yyyy-mm-dd hh24:mi:ss'), t1.name;
select * from tab a
where not exists(select 1 from tab b where a.name=b.name and a.time<b.time)
--借下测试数据
create table ta(id int,name varchar2(20),tm date,num int);
insert into ta values(1 , 'aa',to_date('2009-10-1 11:20:15','yyyy-mm-dd hh24:mi:ss'), 1000);
insert into ta values(2 , 'bb',to_date('2009-10-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(3 , 'aa',to_date('2009-10-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(4 , 'bb',to_date('2009-10-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40 );
insert into ta values(5 , 'cc',to_date('2009-10-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(6 , 'cc',to_date('2009-10-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400 );
insert into ta values(7 , 'aa',to_date('2009-11-1 11:20:15','yyyy-mm-dd hh24:mi:ss'),1000 );
insert into ta values(8 , 'bb',to_date('2009-11-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(9 , 'aa',to_date('2009-11-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(10, 'bb',to_date('2009-11-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40);
insert into ta values(11, 'cc',to_date('2009-11-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(12, 'cc',to_date('2009-11-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400);
select * from ta a
where not exists(select 1 from ta b where a.name=b.name
and to_char(a.tm,'YYYY-MM-DD')=to_char(b.tm,'YYYY-MM-DD') and a.tm<b.tm)
where (name,time) in (
select name,max(time) from tt
group by name,cast(time as varchar(10)))
from (select t.*,
row_number() over(partition by t.name, to_char(t.tm, 'yyyy-mm') order by t.tm desc) rn
from ta t)
where rn = 1
order by id
(select name,time,num,rank()over(partition by name,to_char(time,'yyyymmdd') order by time desc)rk from tablename)a
where a.rk=1 order by a.time desc