SQL虚拟表应用三例
?
SQL虚拟表是一种通过SELECT语句查询常量表达式形成的一个结果集，和数据库的视图、物理表、临时表都差不多。一旦这个虚拟表构造出来，就可以当作实际的表来查询。
?
环境：
Windows XP Professional 简体中文版
mysql-5.0.45-win32
?
应用三例：
?
1、求数字对会计大写的对应表。
SELECT *
? FROM (SELECT 0 AS CODE, '零' AS NAME
??????? UNION
??????? SELECT 1, '壹'
??????? UNION
??????? SELECT 2, '贰'
??????? UNION
??????? SELECT 3, '叁'
??????? UNION
??????? SELECT 4, '肆'
??????? UNION
??????? SELECT 5, '伍'
??????? UNION
??????? SELECT 6, '陆'
??????? UNION
??????? SELECT 7, '柒'
??????? UNION
??????? SELECT 8, '捌'
??????? UNION
??????? SELECT 9, '玖'
??????? UNION
??????? SELECT 10, '拾') AS RMBDX
?ORDER BY CODE ASC;
?
查询结果：
CODE??? NAME
--------------
0?????? 零
1?????? 壹
2?????? 贰
3?????? 叁
4?????? 肆
5?????? 伍
6?????? 陆
7?????? 柒
8?????? 捌
9?????? 玖
10????? 拾
?
?
2、产生0~999之间的数字。
SELECT CAST(CONCAT(CONCAT(N1, N2), N3) AS UNSIGNED INTEGER) AS NUMS
? FROM (SELECT '0' AS N1
??????? UNION
??????? SELECT '1'
??????? UNION
??????? SELECT '2'
??????? UNION
??????? SELECT '3'
??????? UNION
??????? SELECT '4'
??????? UNION
??????? SELECT '5'
??????? UNION
??????? SELECT '6'
??????? UNION
??????? SELECT '7'
??????? UNION
??????? SELECT '8'
??????? UNION
??????? SELECT '9') AS NUM1,
?????? (SELECT '0' AS N2
??????? UNION
??????? SELECT '1'
??????? UNION
??????? SELECT '2'
??????? UNION
??????? SELECT '3'
??????? UNION
??????? SELECT '4'
??????? UNION
??????? SELECT '5'
??????? UNION
??????? SELECT '6'
??????? UNION
??????? SELECT '7'
??????? UNION
??????? SELECT '8'
??????? UNION
??????? SELECT '9') AS NUM2,
?????? (SELECT '0' AS N3
??????? UNION
??????? SELECT '1'
??????? UNION
??????? SELECT '2'
??????? UNION
??????? SELECT '3'
??????? UNION
??????? SELECT '4'
??????? UNION
??????? SELECT '5'
??????? UNION
??????? SELECT '6'
??????? UNION
??????? SELECT '7'
??????? UNION
??????? SELECT '8'
??????? UNION
??????? SELECT '9') AS NUM3
?ORDER BY NUMS ASC;
?
查询结果：
NUMS
---------
0
1
2
3
4
...
998
999
?
?
?
3、求0~999之间整数的二次方根（平方根）。
?SELECT NUMS AS SQUARE, ROUND(SQRT(NUMS)) AS BASIS
? FROM (SELECT CAST(CONCAT(CONCAT(N1, N2), N3) AS UNSIGNED INTEGER) AS NUMS
????????? FROM (SELECT '0' AS N1
??????????????? UNION
??????????????? SELECT '1'
??????????????? UNION
??????????????? SELECT '2'
??????????????? UNION
??????????????? SELECT '3'
??????????????? UNION
??????????????? SELECT '4'
??????????????? UNION
??????????????? SELECT '5'
??????????????? UNION
??????????????? SELECT '6'
??????????????? UNION
??????????????? SELECT '7'
??????????????? UNION
??????????????? SELECT '8'
??????????????? UNION
??????????????? SELECT '9') AS NUM1,
?????????????? (SELECT '0' AS N2
??????????????? UNION
??????????????? SELECT '1'
??????????????? UNION
??????????????? SELECT '2'
??????????????? UNION
??????????????? SELECT '3'
??????????????? UNION
??????????????? SELECT '4'
??????????????? UNION
??????????????? SELECT '5'
??????????????? UNION
??????????????? SELECT '6'
??????????????? UNION
??????????????? SELECT '7'
??????????????? UNION
??????????????? SELECT '8'
??????????????? UNION
??????????????? SELECT '9') AS NUM2,
?????????????? (SELECT '0' AS N3
??????????????? UNION
??????????????? SELECT '1'
??????????????? UNION
??????????????? SELECT '2'
??????????????? UNION
??????????????? SELECT '3'
??????????????? UNION
??????????????? SELECT '4'
??????????????? UNION
??????????????? SELECT '5'
??????????????? UNION
??????????????? SELECT '6'
??????????????? UNION
??????????????? SELECT '7'
??????????????? UNION
??????????????? SELECT '8'
??????????????? UNION
??????????????? SELECT '9') AS NUM3) AS TMP_TAB
?WHERE SQRT(NUMS) = ROUND(SQRT(NUMS))
?ORDER BY SQUARE ASC;
?
查询结果：
?
SQUARE? BASIS
------------------
0?????? 0
1?????? 1
4?????? 2
9?????? 3
16????? 4
25????? 5
36????? 6
49????? 7
64????? 8
81????? 9
100???? 10
121???? 11
144???? 12
169???? 13
196???? 14
225???? 15
256???? 16
289???? 17
324???? 18
361???? 19
400???? 20
441???? 21
484???? 22
529???? 23
576???? 24
625???? 25
676???? 26
729???? 27
784???? 28
841???? 29
900???? 30
961???? 31
?
---- 《完》。
1 楼 hysoft 2008-04-22
隐！！！！！！！