使用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)