SQL 如何取最大值

[herryfanghe21]

ID Product_ID Product_picture
1 1 productImage/1111111105.jpg
784 1 productImage/1111111105_784.jpg
785 1 productImage/1111111105_785.jpg
2 2 productImage/1111121402.jpg
3 2 productImage/1111121402_3.jpg
4 3 productImage/1111121406.jpg
5 3 productImage/1111121406_8.jpg

请问我如何写SQL来取得每一个 Product_ID 组中的ID 为最大的Product_picture
结果应该是：
785 1 productImage/1111111105_785.jpg
3 2 productImage/1111121402_3.jpg
5 3 productImage/1111121406_8.jpg

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (ID int,Product_ID int,Product_picture varchar(31))
insert into #tb
select 1,1,'productImage/1111111105.jpg' union all
select 784,1,'productImage/1111111105_784.jpg' union all
select 785,1,'productImage/1111111105_785.jpg' union all
select 2,2,'productImage/1111121402.jpg' union all
select 3,2,'productImage/1111121402_3.jpg' union all
select 4,3,'productImage/1111121406.jpg' union all
select 5,3,'productImage/1111121406_8.jpg'

select * from #tb t
where not exists(select * from #tb where Product_ID=t.Product_ID and Product_picture>t.Product_picture)

ID          Product_ID  Product_picture
----------- ----------- -------------------------------
785         1           productImage/1111111105_785.jpg
3           2           productImage/1111121402_3.jpg
5           3           productImage/1111121406_8.jpg

(3 行受影响)

[jhzhao2002]

select * from tablename,
(select max(id) as maxid from tablename) as ta
where ID = ta.maxid

[herryfanghe21]

1楼的如果我有几十万条数据，那是不是要写那么多SELECT 啊，
2楼的只能查到最大的啊

[lxq19851204]

Select Max(ID),product_ID,Product_picture From tb Group by product_id

[lxq19851204]

上面的错了

select * from tb where id in (select max(id) from tb group by product_id)

[华夏小卒]

select * from #tb t
where not exists(select * from #tb where Product_ID=t.Product_ID and Product_picture>t.Product_picture)

就这个语句哦，上面那是添加测试数据呢

[阿旭]

select * from 
	(
	select *, max(ID) over (partition by Product_ID)  as MaxID
	from Product
	) Temp
where ID = MaxID

[--小F--]

select * from tb t where id=(select max(id) from tb where product_id=t.product_id)

[阿旭]

[Quote=引用 7 楼 z119977662 的回复:]

SQL code

select * from
(
select *, max(ID) over (partition by Product_ID) as MaxID
from Product
) Temp
where ID = MaxID
[/Quote]

Product是表名。

[liuyizhou1984]

顶四楼，不过四楼的有个错误，改正了下

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (ID int,Product_ID int,Product_picture varchar(31))
insert into #tb
select 1,1,'productImage/1111111105.jpg' union all
select 784,1,'productImage/1111111105_784.jpg' union all
select 785,1,'productImage/1111111105_785.jpg' union all
select 2,2,'productImage/1111121402.jpg' union all
select 3,2,'productImage/1111121402_3.jpg' union all
select 4,3,'productImage/1111121406.jpg' union all
select 5,3,'productImage/1111121406_8.jpg'

Select Max(ID),product_ID,max(Product_picture) From #tb Group by product_id

结果:

785 1 productImage/1111111105_785.jpg
3 2 productImage/1111121402_3.jpg
5 3 productImage/1111121406_8.jpg

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[Product_ID] [int],[Product_picture] [nvarchar](40))
INSERT INTO [tb]
SELECT '1','1','productImage/1111111105.jpg' UNION ALL
SELECT '784','1','productImage/1111111105_784.jpg' UNION ALL
SELECT '785','1','productImage/1111111105_785.jpg' UNION ALL
SELECT '2','2','productImage/1111121402.jpg' UNION ALL
SELECT '3','2','productImage/1111121402_3.jpg' UNION ALL
SELECT '4','3','productImage/1111121406.jpg' UNION ALL
SELECT '5','3','productImage/1111121406_8.jpg'

--SELECT * FROM [tb]

-->SQL查询如下:
SELECT * FROM tb t WHERE id=(SELECT MAX(id) FROM tb WHERE Product_ID=t.Product_ID) 
/*
ID          Product_ID  Product_picture
----------- ----------- ----------------------------------------
785         1           productImage/1111111105_785.jpg
3           2           productImage/1111121402_3.jpg
5           3           productImage/1111121406_8.jpg

(3 行受影响)
*/

[lileiprc]

sql 里面的牛人真多呀