列出所有用户的最后一条记录代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】列出所有用户的最后一条记录
作者:Dezai.CN / 发布于2012/5/10/ 586
有两个表，关联字段是userid，每个用户下面有多条记录，怎么列出所有用户的最后一条记录
<div>
<span style="color: #008080">--</span><span style="color: #008080">一、按name分组取val最大的值所在行的数据。</span><span style="color: #008080">
--</span><span style="color: #008080">方法1：</span><span style="color: #008080">
</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.</span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb a </span><span style="color: #0000ff">where</span><span style="color: #000000"> val </span><span style="color: #808080">=</span><span style="color: #000000"> (</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff00ff">max</span><span style="color: #000000">(val) </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb </span><span style="color: #0000ff">where</span><span style="color: #000000"> name </span><span style="color: #808080">=</span><span style="color: #000000"> a.name) </span><span style="color: #0000ff">order</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> a.name
</span><span style="color: #008080">--</span><span style="color: #008080">方法2：</span><span style="color: #008080">
</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.</span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb a </span><span style="color: #0000ff">where</span><span style="color: #000000"> </span><span style="color: #808080">not</span><span style="color: #000000"> </span><span style="color: #808080">exists</span><span style="color: #000000">(</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb </span><span style="color: #0000ff">where</span><span style="color: #000000"> name </span><span style="color: #808080">=</span><span style="color: #000000"> a.name </span><span style="color: #808080">and</span><span style="color: #000000"> val </span><span style="color: #808080">></span><span style="color: #000000"> a.val)
</span><span style="color: #008080">--</span><span style="color: #008080">方法3：</span><span style="color: #008080">
</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.</span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb a,(</span><span style="color: #0000ff">select</span><span style="color: #000000"> name,</span><span style="color: #ff00ff">max</span><span style="color: #000000">(val) val </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb </span><span style="color: #0000ff">group</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> name) b </span><span style="color: #0000ff">where</span><span style="color: #000000"> a.name </span><span style="color: #808080">=</span><span style="color: #000000"> b.name </span><span style="color: #808080">and</span><span style="color: #000000"> a.val </span><span style="color: #808080">=</span><span style="color: #000000"> b.val </span><span style="color: #0000ff">order</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> a.name
</span><span style="color: #008080">--</span><span style="color: #008080">方法4：</span><span style="color: #008080">
</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.</span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb a </span><span style="color: #0000ff">inner</span><span style="color: #000000"> </span><span style="color: #808080">join</span><span style="color: #000000"> (</span><span style="color: #0000ff">select</span><span style="color: #000000"> name , </span><span style="color: #ff00ff">max</span><span style="color: #000000">(val) val </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb </span><span style="color: #0000ff">group</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> name) b </span><span style="color: #0000ff">on</span><span style="color: #000000"> a.name </span><span style="color: #808080">=</span><span style="color: #000000"> b.name </span><span style="color: #808080">and</span><span style="color: #000000"> a.val </span><span style="color: #808080">=</span><span style="color: #000000"> b.val </span><span style="color: #0000ff">order</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> a.name
</span><span style="color: #008080">--</span><span style="color: #008080">方法5</span><span style="color: #008080">
</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.</span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb a </span><span style="color: #0000ff">where</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> </span><span style="color: #808080">></span><span style="color: #000000"> (</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff00ff">count</span><span style="color: #000000">(</span><span style="color: #808080">*</span><span style="color: #000000">) </span><span style="color: #0000ff">from</span><span style="color: #000000"> tb </span><span style="color: #0000ff">where</span><span style="color: #000000"> name </span><span style="color: #808080">=</span><span style="color: #000000"> a.name </span><span style="color: #808080">and</span><span style="color: #000000"> val </span><span style="color: #808080">></span><span style="color: #000000"> a.val ) </span><span style="color: #0000ff">order</span><span style="color: #000000"> 

</span>

<span style="color: #000000">方法1: 
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID <a.ID) 

方法2: 
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID 

方法3: 
select * from #T a where ID=(select min(ID) from #T where Name=a.Name) 

方法4: 
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5: 
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name) 

方法6: 
select * from #T a where (select count(1) from #T where Name=a.Name and ID <a.ID)=0 

方法7: 
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID) 

方法8: 
select * from #T a where ID!>all(select ID from #T where Name=a.Name) 

方法9(注:ID为唯一时可用): 
select * from #T a where ID in(select min(ID) from #T group by Name) 

--SQL2005: 

方法10: 
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID 

方法11: 

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1 </span>

<span style="color: #000000"><span style="color: #0000ff">select</span><span style="color: #000000"> a.userid,a.username,</span><span style="color: #ff00ff">max</span><span style="color: #000000">(b.head) </span><span style="color: #0000ff">as</span><span style="color: #000000"> head </span><span style="color: #0000ff">from</span><span style="color: #000000"> 
</span><span style="color: #ff00ff">user</span><span style="color: #000000"> a </span><span style="color: #808080">left</span><span style="color: #000000"> </span><span style="color: #808080">join</span><span style="color: #000000"> photo b </span><span style="color: #0000ff">on</span><span style="color: #000000"> a.userid</span><span style="color: #808080">=</span><span style="color: #000000">b.userid
</span><span style="color: #0000ff">group</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> a.userid,a.username</span></span>

/************明细表中重复数据的过滤*******************/
select SID_ID,VWSG_Name from (select *,min(SID_ID)over(partition by VWSG_Name) as MinID from td_VWShopGoodsInDetail) as T where SID_ID=MinID
</div>
试试其它关键字
　列出所有用户的最后一条记录　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)