SQL虚拟表应用三例
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---------01234...998999???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?????? 01?????? 14?????? 29?????? 316????? 425????? 536????? 649????? 764????? 881????? 9100???? 10121???? 11144???? 12169???? 13196???? 14225???? 15256???? 16289???? 17324???? 18361???? 19400???? 20441???? 21484???? 22529???? 23576???? 24625???? 25676???? 26729???? 27784???? 28841???? 29900???? 30961???? 31?---- 《完》。 1 楼 hysoft 2008-04-22 隐！！！！！！！