如何从表中的同一记录列表中获取一条唯一记录?表中没有唯一约束 [英] How to get one unique record from the same list of records from table? No Unique constraint in the table
本文介绍了如何从表中的同一记录列表中获取一条唯一记录?表中没有唯一约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 SQL Server 输出中有一个查询,
I have one query in SQL Server output,
假设我有一张表(Ex.StudentMaster)有一些字段——没有唯一约束.对于前.卷号和名称该表具有相同的数据.例如:
Suppose i have one table (Ex.StudentMaster) having some fields-No unique constraints. For Ex. RollNumber and Name The table has same same data. For ex:
RollNo Name
1 Yoko
1 Yoko
1 Yoko
我只想获得第三条记录.如何识别此唯一记录?
I want to get only third record. How can i identify this unique record?
推荐答案
任何一行都是第三行 :-)
Any row is a third row :-)
create table test
(
n int,
name varchar(30)
);
insert into test values(1,'yoko'),(1,'yoko'),(1,'yoko');
select ROW_NUMBER() over(order by name) as ordinal, * from test;
删除第三"行:-)
with a as
(
select ROW_NUMBER() over(order by name) as ordinal, * from test
)
delete from a where a.ordinal = 3
删除最后一行:
with a as
(
select ROW_NUMBER() over(order by name) as ordinal, * from test
)
delete from a where a.ordinal = (select MAX(ordinal) from a)
这篇关于如何从表中的同一记录列表中获取一条唯一记录?表中没有唯一约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文