使用 NOT IN 排除其他記錄來透過特定條件查詢重複項中的單個值的 MySQL 查詢
首先,我們建立一個表 -
mysql> create table DemoTable ( Id int, FirstName varchar(100) ); Query OK, 0 rows affected (0.69 sec)
使用 insert 命令在表中 插入一些記錄 -
mysql> insert into DemoTable values(100,'Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100,'Robert'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(100,'Mike'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(100,'Sam'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(101,'David'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(101,'Robert'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable values(210,'Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(210,'Bob'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(210,'Sam'); Query OK, 1 row affected (0.17 sec)
使用 select 語句從表中顯示所有記錄 -
mysql> select *from DemoTable;
這將生成以下輸出 -
+------+-----------+ | Id | FirstName | +------+-----------+ | 100 | Chris | | 100 | Robert | | 100 | Mike | | 100 | Sam | | 101 | David | | 101 | Robert | | 210 | Chris | | 210 | Bob | | 210 | Sam | +------+-----------+ 9 rows in set (0.00 sec)
以下查詢可找到具有特定條件的重複項中的單個值 -
mysql> select distinct Id from DemoTable where Id not in ( select Id from DemoTable where FirstName='Chris' );
這將生成以下輸出 -
+------+ | Id | +------+ | 101 | +------+ 1 row in set (0.08 sec)
廣告