第10课:创建新的输出字段(1)

本节要点

数值计算
字段拼接
使用别名

从本节课开始,要学习一些比较复杂的SQL了。

先来看下下面这张表。

这是一张股票交易表(t_stock_trans_dtl),主键是交易编号(trans_id),表中的每一条记录,表示了一条买入或卖出交易,同时记录了交易股票的名称(stock_name)、股票代码(stock_code)、交易时间(opt_tm)、交易类型(opt_typ)、交易价格(price)、交易量(volume)、交易费用(fee)。

其中,股票代码(stock_code)的前两位,代表了是哪个交易所,sh代表上海交易所,sz代表深圳交易所;对于买入交易,交易量为正数,对于卖出交易,交易量为负数;

那么,现在来考虑以下几个查询需求:

手续费保留了5位小数,查询结果只想保留2位小数?
表中只保留了购买单价和数量,那如何取出每笔的交易金额?
如何根据买入数量,区分大单、中单、小单?
表中的操作时间包含了年月日时分秒,只想展示年月日怎么办?

这就需要对表中的原始字段,按照查询需求进行加工后输出。

数值计算

先来讲解下数值计算。考虑下面几个查询需求:

(1)、表中交易数量的单位为股,如何将单位转换为手(1手=100股)后输出?

SELECT
    trans_id,stock_name,price,volume,volume/100
FROM t_stock_trans_dtl;

(2)、如何根据股票交易表中的单价、数量,计算出交易金额(单价*数量)?

SELECT
    trans_id,stock_name,price,volume,price*volume
FROM t_stock_trans_dtl;

上面的两个例子使用了除法和乘法两个运算符。其实,SQL语言也跟数学中的算术运算一样,也支持加、减、乘、除四则运算。而且运算的优先级也是先乘除、后加减。

比如,SELECT a-b*c/d+1等价于SELECT a-((b*C)/d)+1。不过,虽然这两种写法是等价的,我们还是建议使用括号,明确指定计算的优先级。

另外,在SQL语言中,因为不同数据类型的精度不一样,所以导致除法运算时,不一定会出现我们想要的结果。

比如,对于INT类型的字段a,值为1;INT类型的字段b,值为4,而SELECT a/b的值,一定返回0.25吗?这个真不一定!

假如你要某个数据库(比如Teradata数据库)的计算时,看到a/b返回了0,一定不要惊讶,这是因为字段a与b的数据类型为INT导致的。

最后,需要强调一下,加、减、乘、除这四个算术运算符,仅支持数值类型的值进行计算。

字段拼接

仍然看一下例子:

(1)、如何按“洪都航空(sh600316)”展示股票名称和股票代码?也就是说,将股票名称、股票代码这两个字段的值合并后输出?

一般我们查询时,是这样的:

SELECT stock_name,stock_code
FROM t_stock_trans_dtl;

但如果想实现字符串的拼接,就需要使用拼接操作符。不同的数据库,拼接操作符并不完全一样。对于Access、SQL Server数据库,支持的是加号+拼接操作符;

SELECT stock_name + '(' + stock_code + ')'
FROM t_stock_trans_dtl;

对于DB2、Oracle、Teradata数据库,支持的是||拼接操作符;

SELECT stock_name || '(' || stock_code || ')'
FROM t_stock_trans_dtl;

而MySQL数据库干脆不支持拼接操作符,字符串的拼接使用函数CONCAT来实现。

SELECT CONCAT(stock_name,'(',stock_code,')')
FROM t_stock_trans_dtl;

拼接操作符仅支持字符型字段。如果想拼接数值型字段的值,就需要先做一下类型转换,将数值型转换为字符型,然后再拼接。

使用别名

对于我们刚才写出的下面这个拼接的SQL:

SELECT CONCAT(stock_name,'(',stock_code,')')
FROM t_stock_trans_dtl;

这个SELECT的结果集中,CONCAT的结果是没有列名的,或者说就使用了这个CONCAT表达式作为了列名。这样在使用这个结果集时,就不太好引用这个列。

在SQL语言中,支持为复杂的表达式取一个别名,想要使用这个表达式的值时,直接使用别名引用。

使用AS关键字为表达式取别名:

SELECT CONCAT(stock_name,'(',stock_code,')') AS stock
FROM t_stock_trans_dtl;

上面的别名是一个英文单词,也可以使用中文作为别名:

SELECT CONCAT(stock_name,'(',stock_code,')') AS 股票
FROM t_stock_trans_dtl;

甚至于,中文别名中,还可以有空格。不过这时就需要使用引号将别名引起来:

SELECT CONCAT(stock_name,'(',stock_code,')') AS '股票 名称和代码'
FROM t_stock_trans_dtl;

不过,给大家一个建议,尽量不要使用中文别名。如果非要使用,那不论中文别名中是否有空格,都用引号引起来。

课后习题:

1、对于第6课课后习题中的论坛注册用户表,请编写SQL完成以下查询。

(1)、查询所有用户信息,将用户名及手机号合并显示,如“狮子(Tel:13882694448)”。

(2)、根据用户的最近登录时间,查询广东省的用户中,最近有多少天未登录论坛。

(3)、查询每个用户,平均每多天登录一次。如某用户从注册到现在已经经过了100天,但只登录过5,说明该用户平均每20天登录一次。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code