在单个MySQL查询中计算来自不同表的非NULL值
要计算来自不同表的值,语法如下−
Select
(
select count(yourColumnName) from yourTableName1) as anyAliasName1,
(
select count(yourColumnName) from yourTableName2) as anyAliasName2;
让我们先创建一个表−
mysql> create table DemoTable1
-> (
-> Id int
-> );
Query OK, 0 rows affected (1.06 sec)
使用insert命令将数据插入到表中−
mysql> insert into DemoTable1 values(1);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1 values(NULL);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1 values(2);
Query OK, 1 row affected (0.34 sec)
mysql> insert into DemoTable1 values(3);
Query OK, 1 row affected (0.13 sec)
使用select语句显示表中的所有记录−
mysql> select * from DemoTable1;
这将产生以下输出−
+------+
| Id |
+------+
| 1 |
| NULL |
| 2 |
| 3 |
+------+
4 rows in set (0.00 sec)
以下是创建第二个表的查询−
mysql> create table DemoTable2
-> (
-> Id int
-> );
Query OK, 0 rows affected (0.65 sec)
使用insert命令将数据插入到表中−
mysql> insert into DemoTable2 values(10);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable2 values(NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2 values(NULL);
Query OK, 1 row affected (0.08 sec)
使用select语句显示表中的所有记录−
mysql> select * from DemoTable2;
这将产生以下输出−
+------+
| Id |
+------+
| 10 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
以下是获取来自不同表的计数的查询−
mysql> select
-> (
-> select count(Id) from DemoTable1) as CountFirstTableId,
-> (
-> select count(Id) from DemoTable2) as CountSecondTableId
-> ;
这将产生以下输出−
+-------------------+--------------------+
| CountFirstTableId | CountSecondTableId |
+-------------------+--------------------+
| 3 | 1 |
+-------------------+--------------------+
1 row in set (0.00 sec)
阅读更多:MySQL 教程