mysql查询--35分

[v05120s]

mysql 中一个表中字段如下: id value create_time 三个字段
如数据有:
1 test1 2009-9-10
1 test2 2009-9-11
2 testg 2009-9-10
3 testu 2009-9-11
3 testn 2009-9-10
我要想得到的结果是当id相同时，只取create_time为最大的一条数据，这样取得结果会是
1 test2 2009-9-11
2 testg 2009-9-10
3 testu 2009-9-11
请问这个sql语句应该怎么写?

谁先给出正确语句，分全给他......急用中

[liangwansheng]

帮顶。

[liguominz]

select * from 
(select a.*,b.state,b.cTime from a,b where a.id = b.pid order by b.create_time desc) 
as c group by id

a 为第一个表，b为第二个表，C 是临时表

[liguominz]

上面写错了一点

select * from 
(select a.*,b.state,b.create_time from a,b where a.id = b.pid order by b.create_time desc) 
as c group by id

[liusir1990]

select *
from 表名
group by id
having count(*)>1

[wubin_2003]

select id,max(value) as value,max(create_time) as ct
from test2 group by id

[wubin_2003]

select a.id,max(b.create_time),max(a.value1),max(a.value2),max(a.value2),max(b.state) from table_1 a,table_2 b where a.id=b.pid group by a.id

[it_palmer]

select id,max(create_time) as create_time,max(value) as value
from aaa_test
group by id
注：如果你非要把value放到前面，在这个sql外面再套一层查询就可以了。

[guodong66]

帮到你顶下。。呵呵。

[SoLeisure]

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
例二：
select * from testtable
where numeber in (select number from people group by number having count(number) > 1 )
可以查出testtable表中number相同的记录

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people 
where peopleId in (select peopleId from people group by peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段） 
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

[v05120s]

加分了，50分，帮顶也给些分，写些能有用的sql也给分，不过大部分还是给最先写对的人...

[SoLeisure]

select id,max(create_time) create_time from test2 group by id having Count(*) >= 1

[dreamhyz]

楼主参考下以下两个例子 希望有帮助
http://topic.csdn.net/u/20090421/09/d7f2f6e9-d933-43a2-bf32-79607431d9f3.html

http://topic.csdn.net/u/20090514/14/215839ff-eed7-48eb-b424-eab0b0dc95e4.html

[liguominz]

个人觉得先按照时间排序，放到一个临时表中，然后再分组取得时间最大的

[sun201200204]

create table test (user_id number, user_name varchar2(20), opt_time date);
begin
insert into test values(1, 'aa', to_date('2009-01-01 00:10', 'yyyy-mm-dd hh24:mi'));
insert into test values(2 , 'aa', to_date('2009-01-01 00:20', 'yyyy-mm-dd hh24:mi'));
insert into test values(5 , 'aa', to_date('2009-01-01 00:30', 'yyyy-mm-dd hh24:mi'));
insert into test values(10, 'aa', to_date('2009-01-01 00:50', 'yyyy-mm-dd hh24:mi'));
insert into test values(11, 'aa', to_date('2009-01-01 01:20', 'yyyy-mm-dd hh24:mi'));
insert into test values(12, 'aa', to_date('2009-01-01 01:30 ', 'yyyy-mm-dd hh24:mi'));
commit;
end;


select opt_time
from (select to_date('2009-01-01', 'yyyy-mm-dd') + level * 10 / 60 / 24 opt_time
from dual
connect by level <= 12 * 6
union
select to_date('2009-01-01 12:00', 'yyyy-mm-dd hh24:mi') +
level * 10 / 60 / 24 opt_time
from dual
connect by level < 12 * 6)
where opt_time not in (select opt_time from test);

[v05120s]

表1 id value1 value2 value3 id是主键
表2 id pid state create_time id是主键
其中表2中的pid对应表1中的id，我想要查询的结果是表1中的id value1 value1 value1 表2中的state，当表2中，pid相同时只取create_time为最大值的相对应的state..
如表1中存在数据如下:
1 test1 test1 test1
2 test2 test2 test2
3 test3 test3 test3
表2中存在数据如下:
1 1 11 2009-8-9
2 1 12 2009-8-18
3 2 11 2009-8-23
4 2 15 2009-8-10
5 3 18 2009-8-11
6 3 11 2009-8-12
7 2 19 2009-8-13
8 1 32 2009-8-14
9 1 45 2009-8-15
10 3 65 2009-8-16
11 3 56 2009-8-17
我想要得到的结果将会是:
1 test1 test1 test1 12 2009-8-18
2 test2 test2 test2 11 2009-8-23
3 test3 test3 test3 56 2009-8-17
这个sql怎么写？？这个是实际要求。。。不过现在我好像想到解决办法了，去吃饭去了，吃完饭回来结贴!~

[SoLeisure]

select id,max(value) as value,max(create_time) as ct 
from test2 group by id having Count(*) >= 1

漏了value.....

[SoLeisure]

[Quote=引用 8 楼 v05120s 的回复:]
表1 id  value1 value2 value3  id是主键
表2 id  pid state create_time  id是主键
其中表2中的pid对应表1中的id，我想要查询的结果是表1中的id value1 value1 value1 表2中的state，当表2中，pid相同时只取create_time为最大值的相对应的state..
如表1中存在数据如下:
1  test1 test1 test1
2  test2 test2 test2
3  test3 test3 test3
表2中存在数据如下:
1  1  11  2009-8-9
2  1  12  2009-8-18
3  2  11  2009-8-23
4  2  15  2009-8-10
5  3  18  2009-8-11
6  3  11  2009-8-12
7  2  19  2009-8-13
8  1  32  2009-8-14
9  1  45  2009-8-15
10  3  65  2009-8-16
11  3  56  2009-8-17
我想要得到的结果将会是:
1  test1 test1 test1  12  2009-8-18
2  test2 test2 test2  11  2009-8-23
3  test3 test3 test3  56  2009-8-17
这个sql怎么写？？这个是实际要求。。。不过现在我好像想到解决办法了，去吃饭去了，吃完饭回来结贴!~

[/Quote]

两表连接即可