如何查找具有重复字段值的记录

问题描述

我有一个表，其中可能包含一些重复的值，例如

id ........ fullname .......... username ....... dept
1 ........ wayne roo ......... rooney ........ OT
2 ....................... wayne roo ............ wayne ............ RnD
3 ........ alex刺............. alexis ...... xyz
4 ........ harry dsdsd ............魔鬼...... abc
5 .......哈里·凯特.........凯思·哈......... gggg

现在我只想获取那些具有相同全名的记录.

1 ........ wayne roo ......... rooney ........ OT
2 ........ wayne roo ............ wayne ............ RnD.

我想用全名将用户名与全名保持一致
喜欢

韦恩·鲁[rooney]
韦恩·鲁[wayne]
亚历克斯·吉尔
哈里·dsdsd
哈里·凯思

我该怎么办..请帮助...
在此先感谢

i have a table which may contain few duplicate values like

id.................fullname.......... username....... dept
1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD
3.................alex gill..........alexis...............xyz
4.................harry dsdsd............devil...............abc
5................ harry kath............kathhh.........gggg

now i want to fetch only those records with same full name.

1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD.

and i want to concat user name with full name for the records with same full name
like

wayne roo[rooney]
wayne roo[wayne]
alex gill
harry dsdsd
harry kath

how do i do this.. pls help...
thanks in advance

推荐答案

SELECT fullname as fname INTO #temp1 FROM TestTable
GROUP BY 
    fullname
HAVING 
    (COUNT(fullname) > 1); 

SELECT (fullname+ ''['' + username + '']'') from TestTable thetest
right JOIN #temp1 T on thetest.fullname = T.fname

UNION

SELECT fullname
FROM TestTable
GROUP BY fullname
HAVING ( COUNT(fullname) = 1 );

drop TABLE #temp1;

select fullname||username from table where fullname in(
SELECT fullname FROM Table
group by fullname having count(*)>1)
union
select fullname from table 
group by fullname having count(*)=1

试试吧！

Try it !

Select fullname, username, dept from tablename tn
Join tablename on tn.fullname = tablename.fullname and tn.id <> tablename.id

这篇关于如何查找具有重复字段值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！