declare @T table([Name] nvarchar(1),[A] nvarchar(1),[B] nvarchar(1),[C] nvarchar(1)) Insert @T select N'x',N'y',N'z',N'1' union all select N'x',N'y',N'z',N'1' union all select N'x',N'y',N'z',N'1' union all select N'm',N'n',N'r',N'3' union all select N'm',N'n',N'r',N'3' union all select N'i',N'o',N'u',N'8' union all select N'p',N'4',N'r',N's' union all select N'p',N'4',N'r',N's' union all select N'p',N'4',N'r',N's' union all select N'p',N'4',N'r',N's'
;WITH a AS (Select * ,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS row from @T)SELECT [Name],A,B,C FROM a AS b WHERE NOT EXISTS(SELECT 1 FROM a WHERE Name=b.Name AND row>b.row)
/* Name A B C x y z 1 m n r 3 i o u 8 p 4 r s */
---不相同记录 select distinct name from tb order by name
from Item t
where id = (select max(id) from Item where name = t.name)
(select no=row_number() over(partition by name order by getdate()),* from Item) t
where no=1
--写一个不经常用的select
a.*
from
item a join item b
on
a.name=b.name
group by
--这里写ITEM的全部列
having
a.id=max(b.id)
x|y|z|1
x|y|z|1
x|y|z|1 <--找出xyz1输出一笔
m|n|r|3
m|n|r|3 <--找出mnr3输出一笔
i|o|u|8 <--找出iou8输出一笔
p|4|r|s
p|4|r|s
p|4|r|s
p|4|r|s <--找出p4rs输出一笔想要获取:
x|y|z|1
m|n|r|3
i|o|u|8
p|4|r|s
use Tempdb
go
--> -->
declare @T table([Name] nvarchar(1),[A] nvarchar(1),[B] nvarchar(1),[C] nvarchar(1))
Insert @T
select N'x',N'y',N'z',N'1' union all
select N'x',N'y',N'z',N'1' union all
select N'x',N'y',N'z',N'1' union all
select N'm',N'n',N'r',N'3' union all
select N'm',N'n',N'r',N'3' union all
select N'i',N'o',N'u',N'8' union all
select N'p',N'4',N'r',N's' union all
select N'p',N'4',N'r',N's' union all
select N'p',N'4',N'r',N's' union all
select N'p',N'4',N'r',N's'
;WITH a
AS
(Select * ,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS row from @T)SELECT [Name],A,B,C
FROM a AS b WHERE NOT EXISTS(SELECT 1 FROM a WHERE Name=b.Name AND row>b.row)
/*
Name A B C
x y z 1
m n r 3
i o u 8
p 4 r s
*/
select distinct name from tb
order by name
根据楼主的数据 直接distinct就OK了