某个字段查找最大值的记录代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】某个字段查找最大值的记录

作者:蔚兰 / 发布于2014/10/22/ 608


	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

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

同语言下

可能有用的

蔚兰贡献的其它代码(2)