这个distinct有点迷糊，

Question

我想取S.UserId不重复的10条select
top 10 S.userid, S.PartNo, U.stocktime
from
stock_vip S join [user] U on S.UserId=U.id
order by stocktime descuserid                                             PartNo                                                                                               stocktime
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------------------
74511                                              AO3400                                                                                               2010-04-01 11:20:39.000
74511                                              AO3401A                                                                                              2010-04-01 11:20:39.000
74511                                              AO3401A                                                                                              2010-04-01 11:20:39.000
74511                                              AO3404                                                                                               2010-04-01 11:20:39.000
74511                                              AO3409                                                                                               2010-04-01 11:20:39.000
74511                                              AO3413                                                                                               2010-04-01 11:20:39.000
74511                                              AO3415                                                                                               2010-04-01 11:20:39.000
74511                                              AO3416                                                                                               2010-04-01 11:20:39.000
74511                                              AO3418                                                                                               2010-04-01 11:20:39.000
74511                                              AO4184   
select
distinct top 10 S.userid, S.PartNo, U.stocktime
from
stock_vip S join [user] U on S.UserId=U.id
order by stocktime descuserid                                             PartNo                                                                                               stocktime
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------------------
77167                                              1N5908RL4                                                                                            2010-04-01 11:21:57.000
77167                                              2AW7-0001                                                                                            2010-04-01 11:21:57.000
77167                                              2N5087RLRAG                                                                                          2010-04-01 11:21:57.000
77167                                              A1020B                                                                                               2010-04-01 11:21:57.000
77167                                              AD7574JN                                                                                             2010-04-01 11:21:57.000
77167                                              AD7861AP                                                                                             2010-04-01 11:21:57.000
77167                                              ADM236LJN                                                                                            2010-04-01 11:21:57.000
77167                                              ADM693AAN                                                                                            2010-04-01 11:21:57.000
77167                                              ADM693AARN                                                                                           2010-04-01 11:21:57.000
77167                                              ADP1110AN    

Answer 1

select
top 10 S.userid, S.PartNo, U.stocktime
from
stock_vip S join [user] U on S.UserId=U.id
and exists(select 1 
           from stock_vip 
           where userid=s.userid and PartNo>s.PartNo)
order by stocktime desc

Answer 2

select
top 10 S.userid, S.PartNo, U.stocktime
from
stock_vip S join [user] U on S.UserId=U.id
and
 s.PartNo=(select max(PartNo) from stock_vip where userid=s.userid )
order by stocktime desc

Answer 3

;WITH T AS
(
SELECT
   S.USERID, S.PARTNO, U.STOCKTIME,RN=ROW_NUMBER()OVER(PARTITION BY S.USERID ORDER BY GETDATE())
FROM STOCK_VIP S 
JOIN [USER] U ON S.USERID=U.ID
)
SELECT TOP 10 USERID,PARTNO,STOCKTIME
FROM T 
WHERE RN=1

Answer 4

--2000
SELECT TOP 10
   S.USERID, S.PARTNO, U.STOCKTIME
FROM STOCK_VIP S 
JOIN [USER] U ON S.USERID=U.ID
WHERE NOT EXISTS(
SELECT 1
FROM STOCK_VIP 
WHERE USERID=S.USERID AND PARTNO<S.PARTNO)

Answer 5

--tryselect
top 10 S.userid, S.PartNo, U.stocktime
from
(select * from stock_vip a 
where not exists (select 1 from stock_vip where a.partno<partno and a.userid=userid)) S join [user] U on S.UserId=U.id
order by stocktime desc