查询一个表中的两个字段值相同的数据

select LinkUser.* from LinkUser INNER JOIN (SELECT Lname,UserName from LinkUser group by Lname,UserName having count (*)>1) AS B ON LinkUser.Lname =B.Lname AND LinkUser.UserName=B.UserName ORDER BY LinkUser.UserName
SELECT Lname,UserName from LinkUser group by Lname,UserName having count (*)>1 --先找到表中两个字段（Lname,UserName）都一样的值，虚拟成B表，
通过内链接（INNER JOIN）把LinkUser表中两个字段（LinkUser.Lname）都一样的值的数据都查询出来，并按UserName的顺序排列（ORDER BY LinkUser.UserName）。

注意：Lname,UserName两个字段中有NULL时，内链接（INNER JOIN）中inkUser.Lname =B.Lname AND LinkUser.UserName=B.UserName 不成立，因为两空值是不等的。但在SELECT Lname,UserName from LinkUser group by Lname,UserName having count (*)>1的语句中的结果NULL值认定为重复。

--你分组一下就可以得到重复的数据了
SELECT Lname,UserName,count(id) as '在表中重复出现的次数'
where LinkUser
GROUP BY Lname,UserName
HAVING count(id)>1

/*
SELECT *
FROM LinkUser
WHERE ISNULL(Lname,'')=ISNULL(UserName,'') */

按照
Lname
UerName
这2个字段分组，查出来组内条数count(*) 大于1的就是了

/*难道是下面的这种？
select *
from LinkUser
where Lname=UserName; */
意思是去除重复的?
select * from LinkUser a
where exists (select 1 from (select min(id),Lname from LinkUser group by Lname) b
where a.id=b.id)

select Lname,count(id) from LinkUser group by Lname having count(id)>1
select UserName,count(id) from LinkUser group by UserName having count(id)>1