用一条单独的MySQL查询在两个表中找到最高和最低的值?
要从两个表中找到最高和最低值,请使用MAX()和MIN()。由于结果需要从两个表中显示,因此需要使用UNION。让我们首先创建一个表——
mysql> create table DemoTable1
(
UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Score1 int
);
Query OK, 0 rows affected (0.76 sec)
使用插入命令将一些记录插入表中——
mysql> insert into DemoTable1(Score1) values(56);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1(Score1) values(76);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1(Score1) values(65);
Query OK, 1 row affected (0.09 sec)
使用SELECT语句显示表中的所有记录——
mysql> select *from DemoTable1;
这将产生以下输出——
+----------+--------+
| UniqueId | Score1 |
+----------+--------+
| 1 | 56 |
| 2 | 76 |
| 3 | 65 |
+----------+--------+
3 rows in set (0.00 sec)
以下是创建第二个表的查询——
mysql> create table DemoTable2
(
UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Score2 int
);
Query OK, 0 rows affected (0.93 sec)
使用插入命令将一些记录插入表中——
mysql> insert into DemoTable2(Score2) values(67);
Query OK, 1 row affected (0.68 sec)
mysql> insert into DemoTable2(Score2) values(94);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2(Score2) values(98);
Query OK, 1 row affected (0.08 sec)
使用SELECT语句显示表中的所有记录——
mysql> select *from DemoTable2;
这将产生以下输出——
+----------+--------+
| UniqueId | Score2 |
+----------+--------+
| 1 | 67 |
| 2 | 94 |
| 3 | 98 |
+----------+--------+
3 rows in set (0.00 sec)
以下是查找2个表中最高和最低值的查询——
mysql> select max(commonValue) AS Highest_Value,min(commonValue) AS Lowest_Value from
(
select Score1 as commonValue from DemoTable1
union
select Score2 as commonValue from DemoTable2
) tbl;
这将产生以下输出——
+---------------+--------------+
| Highest_Value | Lowest_Value |
+---------------+--------------+
| 98 | 56 |
+---------------+--------------+
1 row in set (0.04 sec)
阅读更多:MySQL 教程