使用MySQL GROUP BY和HAVING显示具有非重复ID的记录

让我们首先创建一个表-

create table DemoTable ( Id int, ColorName varchar(100) );

使用插入命令在表中插入一些记录-

insert into DemoTable values(100,Red); insert into DemoTable values(101,Green); insert into DemoTable values(101,Blue); insert into DemoTable values(102,Yellow); insert into DemoTable values(100,Purple); insert into DemoTable values(100,Black);

使用select语句显示表中的所有记录-

select *from DemoTable;

这将产生以下输出-

+------+-----------+ | Id | ColorName | +------+-----------+ | 100 | Red | | 101 | Green | | 101 | Blue | | 102 | Yellow | | 100 | Purple | | 100 | Black | +------+-----------+ 6 rows in set (0.00 sec)

以下是查询以显示具有非重复ID的记录-

select *from DemoTable where Id in(select Id from DemoTable group by Id having count(*) < 2);

这将产生以下输出-

+------+-----------+ | Id | ColorName | +------+-----------+ | 102 | Yellow | +------+-----------+ 1 row in set (0.07 sec)