查找某个字段最大值的记录 MS SQL
某个字段查找最大值的记录.
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;
-- SQL code
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
根据mysql的手册上面找到一个查询,修改了一下发现也可以实现,但是并不理解什么意思,不知道有没有人给解释一下
SQL codeSELECT *
FROM lk1 l1
WHERE price = (
SELECT MAX ( l2.price )
FROM lk1 l2
WHERE l1.`pid` = l2.`pid` ) ;
-- SQL code
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
阅读全文
-- SQL code
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
根据mysql的手册上面找到一个查询,修改了一下发现也可以实现,但是并不理解什么意思,不知道有没有人给解释一下
SQL codeSELECT *
FROM lk1 l1
WHERE price = (
SELECT MAX ( l2.price )
FROM lk1 l2
WHERE l1.`pid` = l2.`pid` ) ;
-- SQL code
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
类别: 默认分类 查看评论