《案例精解企业级网络构建》赠书活动开始啦！　最新活动：“Web安全大家谈”有奖征文

SQL虚拟表应用三例

2007-08-20 23:29:34

　标签：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

---------

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

---- 《完》。

本文出自 “熔岩” 博客，转载请与作者联系！

上一篇 SQL连接查询深度探险　　下一篇 Hibernate关联关系映射实例速查

类别：MySQL ┆ 技术圈() ┆ 阅读() ┆ 评论() ┆推送到技术圈 ┆返回首页

相关文章

文章评论

[匿名]51CTO游客

2007-08-21 22:05:48

这三例，全是关于数据查询的
貌似第三种全部都是算出来的啊？

leizhimin

2007-08-22 00:10:59

实质上讲，就是一个虚拟表，通过SQL常量查询构造的虚拟表。灵活运用这个有时候会令复杂的问题简单话，比如：有一个查询，根据不同的情况显示不同的值，有时候用case会死人的啊，用这个虚拟表实现就容易多了。

MySQL中是：SELECT ??
Oralce中是：SELECT ?? FROM DUAL

昵称：
验证码：		点击图片可刷新验证码　　博客过2级，无需填写验证码
内容：

熔 岩

SQL虚拟表应用三例

熔岩