SQL虚拟表应用三例

admin 2015年07月24日 MySql学习 892次阅读 查看评论


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
---- 《完》。


« 上一篇 下一篇 » admin原创文章,转载请注明出处! 标签:SQL虚拟表mysql虚拟表

相关日志:

«   2020年11月   »
1
2345678
9101112131415
16171819202122
23242526272829
30
控制面板
您好,欢迎到访网站!
  [查看权限]
网站分类
文章归档
网站收藏
友情链接
图标汇集
  • 又拍云
  • 订阅ipvb的RSS 2.0聚合