select * from tb t where goodsId=(select min(goodsId) from tb where userId=t.userId)
select t.* from tb t where goodsId = (select max(goodsId) from tb where userId = t.userId) select t.* from tb t where goodsId = (select min(goodsId) from tb where userId = t.userId)select t.* from tb t where not exists (select 1 from tb where userId = t.userId and goodsId > t.goodsId) select t.* from tb t where not exists (select 1 from tb where userId = t.userId and goodsId < t.goodsId)
select * from tab a where not exists ( select 1 from tab where userid = a.userid and (goodsId < a.goodsId or goodsId = a.goodsId and status<a.status) )
--方法1 select userId,max(goodsId),max(status)from tb t group by userId --方法2 select * from tb t where goodsId=(select max(goodsId) from tb where userId=t.userId)
create table test ( userid int, goodsid bigint, status int ); insert into test select 56,7362717,1 union select 56,1387123,2 union select 57,2839128,1 union select 57,8574456,1 union select 58,7458121,1----sql server 2000 select * from test a where not exists(select 1 from test b where a.userid=b.userid and a.goodsid>b.goodsid )----sql2005及以上 select userid,goodsid,status from ( select ROW_NUMBER() over(partition by userid order by status) as id,* from test )t where id=1
if object_id('tb','U') is not null drop table tb go create table tb ( userId int not null, goodsId int not null, status int not null ) go insert into tb select 56,7362717,1 union all select 56,1387123,2 union all select 57,2839128,1 union all select 57,8574456,1 union all select 58,7458121,1 go select userId,goodsId,status from tb a where not exists(select 1 from tb where userId=a.userId and goodsId<a.goodsId) go
use students3; go if object_id('Goods')is not null drop table Goods; go create table Goods ( userId int, goodsId bigint, status int check(status in (1,2))) insert into Goods select 56,7362717,1 union all select 56,1387123,2 union all select 57,2839128,1 union all select 57,8574456,1 union all select 58,7458121,1 ; with cte as ( select userId,goodsId,status,row_number()over(partition by userId order by userId)rownum from Goods ) select userId,goodsId,status from cte where rownum=1 ; /* (5 行受影响) userId goodsId status ----------- -------------------- ----------- 56 7362717 1 57 2839128 1 58 7458121 1 */ go drop table Goods
select Max(userId) As userId,Max(goodsId) As goodsId,Max(status) As status From 你的表名 Group By userId
select t.* from tb t where goodsId = (select min(goodsId) from tb where userId = t.userId)select t.* from tb t where not exists (select 1 from tb where userId = t.userId and goodsId > t.goodsId)
select t.* from tb t where not exists (select 1 from tb where userId = t.userId and goodsId < t.goodsId)
where not exists (
select 1
from tab
where userid = a.userid
and (goodsId < a.goodsId
or goodsId = a.goodsId and status<a.status)
)
--方法1
select userId,max(goodsId),max(status)from tb t group by userId
--方法2
select * from tb t where goodsId=(select max(goodsId) from tb where userId=t.userId)
create table test
(
userid int,
goodsid bigint,
status int
);
insert into test
select 56,7362717,1
union
select 56,1387123,2
union
select 57,2839128,1
union
select 57,8574456,1
union
select 58,7458121,1----sql server 2000
select * from test a where not exists(select 1 from test b where a.userid=b.userid and a.goodsid>b.goodsid )----sql2005及以上
select userid,goodsid,status from
(
select ROW_NUMBER() over(partition by userid order by status) as id,* from test
)t where id=1
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
userId int not null,
goodsId int not null,
status int not null
)
go
insert into tb
select 56,7362717,1 union all
select 56,1387123,2 union all
select 57,2839128,1 union all
select 57,8574456,1 union all
select 58,7458121,1
go
select userId,goodsId,status from tb a where not exists(select 1 from tb where userId=a.userId and goodsId<a.goodsId)
go
go
if object_id('Goods')is not null
drop table Goods;
go
create table Goods
(
userId int,
goodsId bigint,
status int check(status in (1,2)))
insert into Goods
select 56,7362717,1 union all
select 56,1387123,2 union all
select 57,2839128,1 union all
select 57,8574456,1 union all
select 58,7458121,1
;
with cte as
(
select userId,goodsId,status,row_number()over(partition by userId order by userId)rownum from Goods
)
select userId,goodsId,status from cte where rownum=1 ;
/*
(5 行受影响)
userId goodsId status
----------- -------------------- -----------
56 7362717 1
57 2839128 1
58 7458121 1
*/
go
drop table Goods