select * from test1 a where ID=(select max(ID) from test1 where product_id=a.product_id)
drop table test --Test datacreate table test( id int, product_id int, user_name varchar(20) )insert into test values(1, 1, 'a') insert into test values(2, 1, 'a') insert into test values(3, 1, 'a') insert into test values(4, 1, 'b') insert into test values(5, 2, 'a') insert into test values(6, 2, 'c') insert into test values(7, 3, 'b') insert into test values(8, 3, 'b') insert into test values(9, 3, 'a') insert into test values(10, 4, 'c') insert into test values(11, 4, 'c') insert into test values(12, 4, 'b')-- end of test--startselect max(id) as id, product_id, (select user_name from test as tb where tb.id = max(ta.id)) from test as ta group by product_id
drop table test --Test datacreate table test( id int, product_id int, user_name varchar(20) )insert into test values(1, 1, 'a') insert into test values(2, 1, 'a') insert into test values(3, 1, 'a') insert into test values(4, 1, 'b') insert into test values(5, 2, 'a') insert into test values(6, 2, 'c') insert into test values(7, 3, 'b') insert into test values(8, 3, 'b') insert into test values(9, 3, 'a') insert into test values(10, 4, 'c') insert into test values(11, 4, 'c') insert into test values(12, 4, 'b')-- end of test--startselect max(id) as id, product_id, (select user_name from test as tb where tb.id = max(ta.id)) as user_name from test as ta group by product_id
declare @tb table(id int,product_id int,user_name varchar(20)) insert into @tb values(1, 1, 'a') insert into @tb values(2, 1, 'a') insert into @tb values(3, 1, 'a') insert into @tb values(4, 1, 'b') insert into @tb values(5, 2, 'a') insert into @tb values(6, 2, 'c') insert into @tb values(7, 3, 'b') insert into @tb values(8, 3, 'b') insert into @tb values(9, 3, 'a') insert into @tb values(10, 4, 'c') insert into @tb values(11, 4, 'c') insert into @tb values(12, 4, 'b')select * from @tb a where not exists (select 1 from @tb where product_id=a.product_id and id>a.id) --结果 /* id product_id user_name ----------- ----------- -------------------- 4 1 b 6 2 c 9 3 a 12 4 b(所影响的行数为 4 行) */
drop table test
--Test datacreate table test(
id int,
product_id int,
user_name varchar(20)
)insert into test values(1, 1, 'a')
insert into test values(2, 1, 'a')
insert into test values(3, 1, 'a')
insert into test values(4, 1, 'b')
insert into test values(5, 2, 'a')
insert into test values(6, 2, 'c')
insert into test values(7, 3, 'b')
insert into test values(8, 3, 'b')
insert into test values(9, 3, 'a')
insert into test values(10, 4, 'c')
insert into test values(11, 4, 'c')
insert into test values(12, 4, 'b')-- end of test--startselect max(id) as id, product_id,
(select user_name from test as tb where tb.id = max(ta.id))
from test as ta
group by product_id
drop table test
--Test datacreate table test(
id int,
product_id int,
user_name varchar(20)
)insert into test values(1, 1, 'a')
insert into test values(2, 1, 'a')
insert into test values(3, 1, 'a')
insert into test values(4, 1, 'b')
insert into test values(5, 2, 'a')
insert into test values(6, 2, 'c')
insert into test values(7, 3, 'b')
insert into test values(8, 3, 'b')
insert into test values(9, 3, 'a')
insert into test values(10, 4, 'c')
insert into test values(11, 4, 'c')
insert into test values(12, 4, 'b')-- end of test--startselect max(id) as id, product_id,
(select user_name from test as tb where tb.id = max(ta.id)) as user_name
from test as ta
group by product_id
insert into @tb values(1, 1, 'a')
insert into @tb values(2, 1, 'a')
insert into @tb values(3, 1, 'a')
insert into @tb values(4, 1, 'b')
insert into @tb values(5, 2, 'a')
insert into @tb values(6, 2, 'c')
insert into @tb values(7, 3, 'b')
insert into @tb values(8, 3, 'b')
insert into @tb values(9, 3, 'a')
insert into @tb values(10, 4, 'c')
insert into @tb values(11, 4, 'c')
insert into @tb values(12, 4, 'b')select * from @tb a
where not exists (select 1 from @tb where product_id=a.product_id and id>a.id)
--结果
/*
id product_id user_name
----------- ----------- --------------------
4 1 b
6 2 c
9 3 a
12 4 b(所影响的行数为 4 行)
*/