如何根据名称字段从表中查找重复值 [英] how find duplicate values from table according to name field ..
问题描述
亲爱的
i有一个包含重复值的表,我想根据
表列''名称查看所有重复值''我希望从表中删除重复的值..
表格如...
开斋节名称
1 a
2 b
3 a
4 c
5 d
6 a
7 d
8 a
9 d
10 e
11 a
12 a
13 a
14 e
请帮我解决上述问题..
[由OP更新:]
i有用此脚本但它显示错误..
选择名称,COUNT(*)来自 t1 COUNT(*)> = 1 group < span class =code-keyword> by name
以下陈述不正确。
选择名称,COUNT(*)来自 t1 COUNT(*) > = 1 group 按名称
这个是因为具有适用于聚合后的中间结果的条件:
选择名称,COUNT(*)来自 t1 group by name COUNT(*)> = 1
但是COUNT(*)不会在这种情况下给0,HAVING是没用的:
选择名称,COUNT(*)来自 t1 group by name
但是如果你不需要计数,只需使用:
t1
Hi Mukesh,
查看示例脚本,
- 表格创建
IF OBJECT_ID (' TempDB .. #Test') IS NOT NULL DROP 表 #Test
CREATE TABLE #Test(eid INT IDENTITY ( 1 , 1 ),name VARCH AR ( 10
0 ))
INSERT INTO #Test(name)
VALUES (' a'),(' b'),(' b' ),(' a'),(' c'),(' d'),(' a'),(' d'),(' a' ),(' d'),(' e'),(' a'), (' a'),(' a'),(' e' )
- 实际数据
SELECT 名称 FROM #Test
- 删除重复记录
DELETE
FROM #Test
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM #Test
GROUP BY 名称)
- 必需输出
SELECT 名称 FROM #Test
问候,
Venkatesh。
用于从中删除重复记录表格你也可以使用
CREATE TABLE #Test(eid INT IDENTITY ( 1 , 1 ),名称 VARCHAR ( 100 ))
INSERT INTO #Test(name)
VALUES (' a'),('跨度>b' ),(' b') ,(' a'),(' c'),(' d'),(' a'),(' d'),(' a'),(' d'),( ' e'),(' a '),(' a'),(' a'),(' e')
cte
as
(
SELECT eid,name,ROW_NUMBER() OVER ( partition by name order by eid) as rowids FROM #Test
)
delete #Test
来自 #Test
join cte on #test.eid = cte.eid
其中 cte.rowids> 1
选择 * 来自 #test
dear
i have a table which contain duplicate values and i want to view all duplicate values according to
table column ''name'' and i want to delete duplicate values from table..
table like...
eid name
1 a
2 b
3 a
4 c
5 d
6 a
7 d
8 a
9 d
10 e
11 a
12 a
13 a
14 e
pls help me to resolve above problem..
[Update by OP:]
i have use this script but it shows error..
select name,COUNT(*) from t1 having COUNT(*)>=1 group by name
The following statement is not correct.
select name,COUNT(*) from t1 having COUNT(*)>=1 group by name
This one is, since having is a condition that applies to the interim result after aggregation:
select name, COUNT(*) from t1 group by name having COUNT(*)>=1
But as COUNT(*) won''t give 0 in this case, HAVING is no use:
select name, COUNT(*) from t1 group by name
But if you don''t need the count, simply use:
select distinct name from t1
Hi Mukesh,
Check the sample Script,
-- Table Creation IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test CREATE TABLE #Test(eid INT IDENTITY(1,1), name VARCHAR(10 0)) INSERT INTO #Test(name) VALUES('a'),('b'),('b'), ('a'), ('c'), ('d'), ('a'), ('d'), ('a'), ('d'), ('e'), ('a'), ('a'), ('a'), ('e') -- Actual Data SELECT Name FROM #Test -- Delete Duplicate Records DELETE FROM #Test WHERE ID NOT IN ( SELECT MAX(ID) FROM #Test GROUP BY Name) -- Required Output SELECT Name FROM #Test
Regards,
Venkatesh.
For Deleteing the duplicate records from the table you can also use
CREATE TABLE #Test(eid INT IDENTITY(1,1), name VARCHAR(100)) INSERT INTO #Test(name) VALUES('a'),('b'),('b'), ('a'), ('c'), ('d'), ('a'), ('d'), ('a'), ('d'), ('e'), ('a'), ('a'), ('a'), ('e') with cte as ( SELECT eid,name,ROW_NUMBER() OVER(partition by name order by eid) as rowids FROM #Test ) delete #Test from #Test join cte on #test.eid = cte.eid where cte.rowids > 1 select * From #test
这篇关于如何根据名称字段从表中查找重复值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!