34,588
社区成员
发帖
与我相关
我的任务
分享
<table>
<tr><td></td>no<td></td><td>stuNo</td><td>stuName</td></tr>
<tr><td></td>1<td></td><td>22</td><td>ww</td></tr>
<tr><td></td>2<td></td><td>22</td><td>ww</td></tr>
<tr><td></td>3<td></td><td>44</td><td>mm</td></tr>
</table>
在SQL Server中如何查询得出,让重复的数据只显示一次
<table>
<tr><td></td>no<td></td><td>stuNo</td><td>stuName</td></tr>
<tr><td></td>1<td></td><td>22</td><td>ww</td></tr>
<tr><td></td>3<td></td><td>44</td><td>mm</td></tr>
</table>
distinct关键字就可以了
use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([no] INT,[stuNo] INT,[stuName] varchar(50))
insert into tb
select 1,22,'ww' union all
select 2,22,'ww' union all
select 3,44,'mm'
go
--select * from tb
--显示最小的
select * from tb t
where not exists(select 1 from tb where stuNo=t.stuNo and stuName=stuName and [no] < t.no)
/*
no stuNo stuName
----------- ----------- --------------------------------------------------
1 22 ww
3 44 mm
(2 行受影响)
*/
--显示最大的
select * from tb t
where not exists(select 1 from tb where stuNo=t.stuNo and stuName=stuName and [no] > t.no)
/*
no stuNo stuName
----------- ----------- --------------------------------------------------
2 22 ww
3 44 mm
*/
select distinct * from tb