56,914
社区成员




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
首先通过下面的查询
select pid from lk1 group by pid;
这样可以获取所有的pid,
然后在程序中遍历这个结果,循环执行下面的查询
select * from lk1 WHERE pid = 遍历得到的pid ORDER BY price desc LIMIT 0,1;
就获取了所有产品中最大价格的用户信息
就是因为这种效率实现太低,想优化一下
1 1 200 2007-07-01
1 2 200 2007-07-02
6 3 130 2007-07-04
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
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` ) ;
SELECT *
FROM lk1 l1
WHERE price = (
SELECT MAX( l2.price )
FROM lk1 l2
WHERE l1.`pid` = l2.`pid` ) ;
select lk2.* from (selcet * from lk1 order by price desc) as lk2 group by lk2.pid;
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
这样也行的。
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
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
现有数据库某表内容如下:
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的记录