MySQL 如何在MySql中使用实际行数(COUNT(*))作为WHERE子句而不编写子查询

用where子句实现。

语法如下

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName
WHERE
(
   SELECT COUNT(*) FROM yourTableName
)=2;

为了理解这个概念，让我们创建一个表。创建表的查询语句如下

mysql> create table CountWithSubqueryDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   - > EmployeeName varchar(20)
   - > );
Query OK, 0 rows affected (2.09 sec)

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

查询如下

mysql> insert into CountWithSubqueryDemo(EmployeeName) values('John');
Query OK, 1 row affected (0.54 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.36 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Carol');
Query OK, 1 row affected (0.23 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('');
Query OK, 1 row affected (0.31 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Bob');
Query OK, 1 row affected (1.24 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.30 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Mike');
Query OK, 1 row affected (0.18 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James');
Query OK, 1 row affected (0.26 sec)

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

查询如下

mysql> select *from CountWithSubqueryDemo;

以下是输出结果

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | John         |
|  2 | NULL         |
|  3 | Carol        |
|  4 |              |
|  5 | Bob          |
|  6 | NULL         |
|  7 | Sam          |
|  8 | Mike         |
|  9 | James        |
+----+--------------+
9 rows in set (0.19 sec)

以下是如何在where子句中使用实际行数的方法

mysql> select Id,EmployeeName from CountWithSubqueryDemo
   - > where
   - > (
   - > select count(*) from CountWithSubqueryDemo
   - > )=2;
Empty set (0.03 sec)

在上面的查询中，我们得到了空集，因为它只返回当表中只有两条记录时才返回。因此，让我们从表中删除所有记录，并插入2条记录。

查询如下

mysql> truncate table CountWithSubqueryDemo;
Query OK, 0 rows affected (1.95 sec)

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

查询如下

mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James');
Query OK, 1 row affected (0.15 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.46 sec)

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

查询如下

mysql> select *from CountWithSubqueryDemo;

以下是输出结果

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | James        |
|  2 | NULL         |
+----+--------------+
2 rows in set (0.00 sec)

现在以下是在where子句中使用实际行数的查询语句

mysql> select Id,EmployeeName from CountWithSubqueryDemo
   - > where
   - > (
   - > select count(*) from CountWithSubqueryDemo
   - > )=2;

以下是输出结果

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | James        |
|  2 | NULL         |
+----+--------------+
2 rows in set (0.00 sec)