根据某个字段查找最大值的记录

[lazyboy_wu]

现有数据库某表内容如下:

uid pid price time
1 1 100 2007-07-01
1 2 150 2007-07-02
2 1 110 2007-07-03
3 1 120 2007-07-04
4 2 180 2007-07-04
3 2 170 2007-07-04
6 3 130 2007-07-04


怎么查询才能获得如下的查询结果
uid pid price time
3 1 120 2007-07-04
4 2 180 2007-07-04
6 3 130 2007-07-04

也就是查出pid的最大price的记录

[lazyboy_wu]

现有数据库某表内容如下:

uid	pid	price	time
1	1	100	2007-07-01
1	2	150	2007-07-02
2	1	110	2007-07-03
3	1	120	2007-07-04
4	2	180	2007-07-04
3	2	170	2007-07-04
6	3	130	2007-07-04


怎么查询才能获得如下的查询结果
uid	pid	price	time
3	1	120	2007-07-04
4	2	180	2007-07-04
6	3	130	2007-07-04

也就是查出pid的最大price的记录

[gywain]

稀里糊涂，没有看明白你的意思，你是找price的最大值还是什么，查询的条件是什么？

[wildlily980]

select table_name.* from table_name,(select max(price) as price,pid from table_name group by pid) as table_name_temp where table_name_temp.price=table_name.price and table_name_temp.pid=table_name.pid;
试试。

[懒得去死]

方法很多的。

create table lk1 (
uid int,
pid int,
price int,
`time` date
)engine=myisam;
insert into lk1 values
(1, 1, 100, '2007-07-01'),
(1, 2, 150, '2007-07-02 '),
(2, 1, 110, '2007-07-03 '),
(3, 1, 120, '2007-07-04 '),
(4, 2, 180, '2007-07-04 '),
(3, 2, 170, '2007-07-04 '),
(6, 3, 130, '2007-07-04 ');
select * from (select * from lk1 order by uid desc) T group by pid;

结果：

query result(3 records)
uid pid price time 
3 1 120 2007-07-04 
4 2 180 2007-07-04 
6 3 130 2007-07-04 

[懒得去死]

这样也行的。

select max(uid),max(pid),max(price),max(`time`) from lk1 group by pid;


query result(3 records)
max(uid) max(pid) max(price) max(`time`) 
3 1 120 2007-07-04 
4 2 180 2007-07-04 
6 3 130 2007-07-04 

[lazyboy_wu]

to yueliangdao0608(骑着上帝看戏)
还有一个疑问如果price的字段中有重复字段,发现还是没有办法正确获取

uid  pid  price  time  
1 1 200 2007-07-01 
1 2 200 2007-07-02 
2 1 110 2007-07-03 
3 1 120 2007-07-04 
4 2 180 2007-07-04 
3 2 170 2007-07-04 
6 3 130 2007-07-04 
4 3 200 2007-07-05 
5 3 210 2007-07-05 

你给我的最新查询的结果是

uid  pid  price  time  
1 1 200 2007-07-01 
1 2 200 2007-07-02 
4 3 200 2007-07-05 

但是实际内容应该是
uid  pid  price  time  
1 1 200 2007-07-01 
1 2 200 2007-07-02 
5 3 210 2007-07-05 

[lazyboy_wu]

根据你给我的思路,写一个最新的查询

select lk2.* from (selcet * from lk1 order by price desc) as lk2 group by lk2.pid;

[lazyboy_wu]

根据mysql的手册上面找到一个查询,修改了一下发现也可以实现,但是并不理解什么意思,不知道有没有人给解释一下

SELECT * 
FROM lk1 l1
WHERE price = ( 
SELECT MAX( l2.price ) 
FROM lk1 l2
WHERE l1.`pid` = l2.`pid` ) ;

[懒得去死]

不好意思，我一直用UID排的序，应该用PRICE字段排序的。我第一次写的修改一下就可以了。

create table lk1 (
uid int,
pid int,
price int,
`time` date
)engine=myisam;
insert into lk1 values
(1, 1, 100, '2007-07-01'),
(1, 2, 150, '2007-07-02 '),
(2, 1, 110, '2007-07-03 '),
(3, 1, 120, '2007-07-04 '),
(4, 2, 180, '2007-07-04 '),
(3, 2, 170, '2007-07-04 '),
(6, 3, 130, '2007-07-04 ');

select * from (select * from lk1 order by price desc) T group by pid;

结果1：
query result(3 records)
uid pid price time 
3 1 120 2007-07-04 
4 2 180 2007-07-04 
6 3 130 2007-07-04 

truncate table lk1;

insert into lk1 values
(1, 1, 200, '2007-07-01'),
(1, 2, 200, '2007-07-02 '),
(2, 1, 110, '2007-07-03 '),
(3, 1, 120, '2007-07-04 '),
(4, 2, 180, '2007-07-04 '),
(3, 2, 170, '2007-07-04 '),
(6, 3, 130, '2007-07-04 ');

select * from (select * from lk1 order by price desc) T group by pid;

结果2：

query result(3 records)
uid pid price time 
1 1 200 2007-07-01 
1 2 200 2007-07-02 
6 3 130 2007-07-04 

insert into lk1 values
(4, 3, 200, '2007-07-05 '),
(5, 3, 210, '2007-07-05' );
select * from (select * from lk1 order by price desc) T group by pid;

结果3：
query result(3 records)
uid pid price time 
1 1 200 2007-07-01 
1 2 200 2007-07-02 
5 3 210 2007-07-05 
LS兄弟写的也可以，意思即是所谓的相关子查询。

如果想效率高点的话就要用这句：
select * from (select * from lk1 order by price desc) T group by pid;
因为这个里面涉及到自查询。
不过如果数据不多的话，两个随便用哪个都行。
SELECT * 
FROM lk1 l1
WHERE price = ( 
SELECT MAX( l2.price ) 
FROM lk1 l2
WHERE l1.`pid` = l2.`pid` ) ;

[懒得去死]

呵呵。刚才看到原来LS的LS就是LZ啊。不好意思。

[lazyboy_wu]

to yueliangdao0608(骑着上帝看戏) 你这样的结果只是类似我给你的结果,而不是我要的查询!

假如内容是这样的话,就查不出来了

1    1    200    2007-07-01
1    2    200    2007-07-02
2    1    110    2007-07-03
3    1    120    2007-07-04
4    2    180    2007-07-04
3    2    170    2007-07-04
6    3    130    2007-07-04

你的查询结果是
3 1 200 2007-07-04 
4 2 200 2007-07-04 
6 3 130 2007-07-04 

但是实际应该是

1 1 200 2007-07-04 
1 2 200 2007-07-04 
6 3 130 2007-07-04 

[lazyboy_wu]

上面的日期没有改,实际结果是

1 1 200 2007-07-01 
1 2 200 2007-07-02 
6 3 130 2007-07-04 

[lazyboy_wu]

这个表的结构是
用户(uid)购买某项产品(pid)的价格(price)记录,当然一个人可以买很多产品,一个产品也可能被很多人买,

现在的要求是查出已经购买的所有产品中最大价格的用户信息

我的实现是通过程序实现的:

首先通过下面的查询

select pid from lk1 group by pid;

这样可以获取所有的pid,

然后在程序中遍历这个结果,循环执行下面的查询

select * from lk1 WHERE pid = 遍历得到的pid ORDER BY price desc LIMIT 0,1;

就获取了所有产品中最大价格的用户信息

就是因为这种效率实现太低,想优化一下

[懒得去死]

不好意思。这样就行了。

create table lk1 (
uid int,
pid int,
price int,
`time` date
)engine=myisam;
insert into lk1 values
(1, 1, 100, '2007-07-01'),
(1, 2, 150, '2007-07-02 '),
(2, 1, 110, '2007-07-03 '),
(3, 1, 120, '2007-07-04 '),
(4, 2, 180, '2007-07-04 '),
(3, 2, 170, '2007-07-04 '),
(6, 3, 130, '2007-07-04 ');

select * from lk1 where price in (select max(price) from lk1 group by pid) group by pid;

结果1：
query result(3 records)
uid pid price time 
3 1 120 2007-07-04 
4 2 180 2007-07-04 
6 3 130 2007-07-04 

truncate table lk1;

insert into lk1 values
(1, 1, 200, '2007-07-01'),
(1, 2, 200, '2007-07-02 '),
(2, 1, 110, '2007-07-03 '),
(3, 1, 120, '2007-07-04 '),
(4, 2, 180, '2007-07-04 '),
(3, 2, 170, '2007-07-04 '),
(6, 3, 130, '2007-07-04 ');

select * from lk1 where price in (select max(price) from lk1 group by pid) group by pid;

结果2：

query result(3 records)
uid pid price time 
1 1 200 2007-07-01 
1 2 200 2007-07-02 
6 3 130 2007-07-04