转自https://blog.csdn.net/weixin_44145478/article/details/107079176

mysql文档中对于dual表的解释：

You are allowed to specify DUAL as a dummy table name in situations
where no tables are referenced: 你可以在没有表的情况下指定一个虚拟的表名 mysql> SELECT 1 +
1 FROM DUAL;
-> 2 DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL
may ignore the clauses. MySQL does not require FROM DUAL if no tables
are referenced.
DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。

1 select 7*9 from dual;     计算器
2 SELECT SYSDATE() from dual     获取系统时间

案例
开发过程中,需要对数据进行excel展示或导出,并且需要各种类型的数据,这里提供一种通过dual表来实现

创建表teacher,插入数据

1 CREATE TABLE `teacher` (
2   `id` bigint(20) NOT NULL AUTO_INCREMENT,
3   `name` varchar(10) DEFAULT NULL,
4   `age` int(11) DEFAULT NULL,
5   `score` int(10) DEFAULT NULL,
6   PRIMARY KEY (`id`),
7   KEY `index_three_key` (`name`,`age`,`score`)
8 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='老师';

1 INSERT INTO teacher(id,name,age,score) VALUES(1,'Alen',32,98),(2,'Aony',43,1),
2 (3,'Dansy',53,78),(4,'Fulanke',25,65),(5,'Tom',32,54)

 1 SELECT
 2     '员工汇总' AS id,
 3     '' AS NAME,
 4     '' AS age,
 5     '' AS score 
 6 FROM
 7 DUAL UNION ALL
 8 SELECT
 9     * 
10 FROM
11     teacher UNION ALL
12 SELECT
13     '平均值' AS id,
14     '' AS NAME,
15     '' AS age,
16     cast(
17     AVG( score ) AS DECIMAL ( 10, 3 )) AS score 
18 FROM
19     teacher UNION ALL
20 SELECT
21     '' AS id,
22     '' AS NAME,
23     '' AS age,
24     '' AS score 
25 FROM
26 DUAL UNION ALL
27 SELECT
28     '绩效' AS id,
29     '' AS NAME,
30     '' AS age,
31     '' AS score 
32 FROM
33 DUAL UNION ALL
34 SELECT
35     'A(评分)' AS id,
36     'B(评分)' AS NAME,
37     'C(评分)' AS age,
38     'D(评分)' AS score 
39 FROM
40 DUAL UNION ALL
41 SELECT
42     ( SELECT COUNT( score ) FROM teacher WHERE score >= 90 ) AS id,
43     ( SELECT COUNT( score ) FROM teacher WHERE score >= 80 AND score < 90 ) AS NAME,
44     ( SELECT COUNT( score ) FROM teacher WHERE score >= 60 AND score < 80 ) AS age,
45     ( SELECT COUNT( score ) FROM teacher WHERE score < 60 ) AS score 
46 FROM
47 DUAL