Question

SQL查询两个字段相同记录或者查询连个字段不同的记录

比如有数据库 tb 字段 name dw sex id
我要查询 字段 name 与字段 dw 都相同的所有记录

比如这条语句 select * form tb where name in(select name form tb where group by name having count(*)=1) 能实现 name 字段所有相同的记录 但不知道这么修改才能实现 字段 name 与字段 dw 都相同的所有记录
select * from tb where name in (SELECT name FROM tb GROUP BY name HAVING COUNT(name)=1)
AND dw IN
(SELECT dw FROM tb GROUP BY dw HAVING COUNT(dw)=1); 应该是这样的 wubolu兄弟的想法是正确的

Answer 1

你试试,希望能帮到你：

SELECT name,dw FROM tb GROUP BY name,dw HAVING COUNT(name)>=2
AND dw IN
(SELECT dw FROM tb GROUP BY dw HAVING COUNT(dw)>=2);

如果你还想看到NAME和DW字段都相同的出现次数你可以加入COUNT(NAME),如:
SELECT name,dw,COUNT(name) FROM tb GROUP BY name,dw HAVING COUNT(name)>=2
AND dw IN
(SELECT dw FROM tb GROUP BY dw HAVING COUNT(dw)>=2);

Answer 2

select * from tb where name = dw