1.4 数据列与表达式列

1.4.1 数据列

之前在创建或修改数据表结构时,用到的都是普通数据列,这些列都可直接输入数据,所输入的全部内容也都将物理保存在项目文件中。

1.4.2 表达式列

所谓的表达式列,其内容是由一个表达式计算得出的。这些内容并不会进行物理保存,仅在运行时动态生成。这和Excel中的公式计算是一样的道理。

以Excel的订单表为例,假如希望通过单价、折扣和数量得到金额的值,可以先在G2单元格设置计算公式,然后按住G2单元格右下角的加号(填充柄)向下拖曳即可得到结果(或者双击填充柄),如下图所示。

拖曳或双击后得到的金额数值如下图所示。

此时,如果单击金额列的任意一个单元格,其上方的编辑栏显示的都是计算公式而不是具体的值,这就表明该列数据并不是物理存在的,而是根据其他列的内容动态生成的。

Foxtable的表达式列也是同样的道理。

例如,在订单表中增加一个表达式列:单击【数据表】功能区的【表相关】功能组中的【其他】命令按钮,选择下拉菜单中的【查看表结构】命令,或者直接单击【数据表】功能区的【列相关】功能组中的【增加列】按钮,选择下拉菜单中的【表达式列】命令,如下图所示。

弹出对话框如下图所示,先设置列名和数据类型。

然后单击“表达式”输入框右侧的【…】按钮,打开【表达式生成器】对话框,如下图所示。

其中,表达式编辑框下方的一排按钮为常用运算符,单击即可添加到编辑框中;下方左侧列表框列出的为常用函数,中间列表框列出的为当前数据表已经存在的数据列,双击均可添加到编辑框中;下方右侧列表框列出的为在线帮助,双击相应选项可查看具体的帮助内容。

需注意,字段名称列表中的“_Identify”和“_Locked”是隐藏列,其作用在之前的“创建数据表结构”一节中有过详细讲解。

表达式编辑完成后,单击【确定】按钮,将生成一个表达式列,如下图所示。

上图中,“金额”列的值就是根据其他列的值动态生成的。只要相关列的数据发生变化,该列数据也会自动即时更新。当需要修改表达式列的列名、标题或数据类型时,可以单击菜单上的【更改列】命令按钮;当需要修改表达式时,可单击【fx表达式】命令按钮。

注意:当表达式列没有设置计算公式时,虽然可以输入数据,但这些数据并不会被保存;已经设置了计算公式的表达式列,虽然手工可以修改列中的数据,但按“Tab”键或“Enter”键确认时仍然会自动还原为公式计算。

表达式不仅可以用于生成表达式列,还可用于条件设置等其他场合。例如,复制“订单”表时,可同时设置要复制的数据条件,如下图所示。

需注意,这里设置的条件只能使用数据列,不能使用表达式列。如上图所示,假如将条件改为:

[金额] > 500

单击【确定】按钮将不会有任何结果,这是因为“金额”列是表达式列!而且,在复制完成的数据表中表达式列不会被复制。

1.4.3 表达式编写规则

表达式由运算符、列名称及函数组成。首先说明一点,这些都是不区分大小写的。例如,运算符“In”可写成“in”,列名“产品ID”可写成“产品id”,函数“Len”可写成“len”等。

❶运算符

如同前面的“表达式生成器”对话框所示,常用的运算符有20个,实际数量比20个还要多。具体可分为以下几类。

● 算术运算符

此类运算符共有5个,分别为+(加)、-(减)、*(乘)、/(除)、%(取余数)。

其中,前4个都很好理解,第5个表示取余数:当使用%计算的结果为0时,表明前一个数可以被后一个数整除。例如,5 % 2的结果为1,表示5除以2的余数是1。

需要注意的是,使用%时,前后两个数字必须是整数,如51%2就会提示错误。

再如,将订单表中的“金额”列表达式改为:

[折扣] % [单价]

同样也会出错。这是因为折扣和单价列并不完全是整数。

● 比较运算符

此类运算符共有8个,一般需要配合表达式函数来使用。具体包括=(等于)、<(小于)、>(大于)、<=(小于等于)、>=(大于等于)、<>(不等于)、In(包含)、Like(匹配)。

其中,前6个运算符同样很好理解,例如:

[数量] < 100

现重点学习后面两个运算符。

○ In:用来判断某一个值是否为指定的多个值中的任何一个

例如,以下3个表达式就分别使用了字符、数值和日期3种数据类型进行判断,以表示指定列的值是否在所列举的范围之中:

[产品ID] In ('P01','P02','P03')

[数量] In (170,500,820)

[日期] In ('7/2/2012', #7/12/2012#, #2012-8-10#)

很显然,当使用In时,如果指定值为字符,要用单引号括起来;如果为日期,可用单引号或#号,日期值可以是“年-月-日”,也可以是“月/日/年”;其他类型数值可直接写。当判断的内容中有英文字母时,是不区分大小写的。例如,上面的第一行代码,当“产品ID”为p01、p02或p03时,也是符合判断条件的。

当指定的字符本身就带有单引号时,编写表达式时要用两个单引号代替。例如,以下代码表示判断标题名称是否为“你在用foxtable吗”:

[标题名称] In ('你在用''foxtable''吗')

由于这里只有一个判断值,因此也可以写成:

[标题名称] =‘你在用''foxtable''吗'

如果在In运算符之前加上Not关键词,表示不在指定的数值中。例如:

[产品ID] Not In ('p01','p02','p03')

○ Like:使用“*”或“%”通配符比较字符串

在这种比较中,“*”和“%”是可以互换的,表示任意个数的字符,且只能用在开头或者结尾。例如,判断以“P”开头的“产品ID”:

[产品ID] Like 'P%'

再如,判断是否以单引号结尾:

[产品ID] Like '*'''

需注意,在字符串的中间是不允许使用通配符的。如果要将通配符作为比较内容的一部分,必须使用中括号括起来,这样就会将“*”和“%”还原为普通的字符。例如,判断“产品ID”是否以“A*”开头:

[产品ID] Like 'A[*]%'

和In一样,Like运算符的前面也可以加上Not关键词,表示不类似的意思。例如:

[产品ID] Not Like '*A*'

● 连接运算符

在之前所举的表达式示例中,已经多次用到这种连接运算符。例如,[]用于连接列名称或者将“*”“%”还原为普通字符,单引号“‘”用于连接字符串,“#”用于连接日期。此外,还有以下几种常用的连接运算符。

():用于组合表达式或者强制改变优先级。例如,之前用于计算金额列的表达式为:

[数量] * [单价] * (1 - [折扣])

And:前后连接的表达式必须同时满足。例如,“产品ID”以P开头、同时数量小于500:

[产品ID] Like 'P%' And [数量] < 500

Or:前后连接的表达式只需满足一个即可。例如,“产品ID”以P开头或者数量小于500:

[产品ID] Like 'P%' Or [数量] < 500

计算表达式值时And优先于Or,可使用括号来强制改变这种优先级。例如:

([产品ID] ='P01' Or [产品ID] ='P02') And [数量] < 500

❷列名称

当在表达式中需要用到列名时,列名称最好用方括号括起来,尽管这并不是必需的。

但是,当列名称中含有一些特殊字符时,列名则必须用中括号括起来。关于这一点将在第3章中再作详细说明。

❸表达式函数

在“表达式生成器”对话框中,下方左侧的列表框就是一些常用的表达式函数。具体可分为以下几种。

● 聚合函数

此类函数一般用于数据统计,共有7个,即Sum(求和)、Avg(平均)、Min(最小值)、Max(最大值)、Count(计数)、Var(方差)、St Dev(标准偏差)。

其中,最后两个函数是用于测量数值型数据离散程度的重要指标,一般用于专业性较强的数理统计,日常工作中很少用到,基本可以忽略。

需要说明的是,表达式中的聚合函数很少在单独的数据表中使用,它一般用于关联表的统计。关于这方面的知识将在“表间关联”一节中学习。

● IIF函数

该函数使用频率很高,如果表达式的计算结果为True,则返回一个指定值;否则将返回另一个指定值。例如,将“金额”列的表达式修改为:

IIF([单价] = 0, Null ,[单价] * (1 - [折扣]) * [数量])

该表达式的意思是,如果“折扣”列的值为0,那么“金额”就设置为Null;否则就以单价、折扣、数量三列的乘积生成“金额”值。

在列表达式中,Null用于表示空值,也就是什么都没有。

● Is Null函数

该函数用于判定一个指定表达式的值是否为空值。如果不为空,则返回表达式的值;否则返回一个替代值。例如,将下图所示的第3行和第5行“折扣”为0的值按“Delete”键删除,它们就变成了空值。

然后将"金额"列的表达式修改为:

[单价] * (1 - [折扣]) * [数量]。

执行后发现,第3行和第5行“金额”列的值仍然是空。此种情况表明,只要表达式中存在空值,那么它的计算结果肯定也为空。如果希望将空值列正常参与运算,有以下两种处理方法。

第一种,使用IIF函数:

[单价] * (1 - IIF([折扣] Is Null, 0,[折扣])) * [数量]

该表达式的重点在于加粗的部分:如果“折扣”为空值,就以0替代;否则仍然取原来的折扣值。如果判断不为空,可使用Is Not Null。

第二种,使用Is Null函数:

[单价] * (1 - Is Null([折扣],0)) * [数量]

这里Is Null函数的意思是:如果“折扣”为空,那么就以0替代;否则仍然用原来的折扣值。

很显然,使用Is Null语句返回的是一个逻辑值;而使用Is Null函数则直接返回具体的数值。虽然仅有一个空格之差,但意义却完全不同!

以上两种方式的处理结果都是一样的,如下图所示。

● 字符处理函数

这方面的表达式函数有3个,即Trim(移除字符串的前后空格)、Substring(从字符串中的指定位置开始返回指定长度的子字符串)、Len(返回字符串的长度)。

例如,再增加一个临时的字符型表达式列,如下图所示。

这里表达式的意思是,取“产品ID”列和“客户ID”列的前两个字符并进行拼接(Substring的起始位置是从1开始的)。生成的表达式列效果如下图所示。

● Convert转换函数

该函数可以将表达式转换为指定的数据类型。其语法格式为:

Convert(expression,type)

其中,参数expression为要转换的表达式;type为转换成的数据类型。可转换的数据类型包括以下几种。

System String:字符型

System Date Time:日期时间型

System Boolean:逻辑型

System Byte:微整数

System Int16:短整数

System Int32:整数

System Int64:长整数

System Single:单精度小数

System Double:双精度小数

System Decimal:高精度小数

其中,只有“System Int64”和Foxtable数据表中的列类型不存在对应关系。该类型表示长整数,其值介于-9 223 372 036 854 775 808~+9 223 372 036 854 775 807之间。在Foxtable数据表中,此范围的整数一般是用双精度小数代替。

Convert函数除了可以转换数据类型外,还可变相实现一些其他功能。例如,列表达式并没有提供专门的四舍五入函数,只能用Convert函数来间接实现。假如将金额列保留两位小数,表达式可修改为:

Convert([单价] * (1 - Is Null([折扣],0)) * [数量] * 100, 'System.Int64')/100

该表达式的意思是,先将得到的计算结果乘以100,再转换为长整数;然后再以这个长整数除以100,得到的数据肯定最多只有两位小数。同理,如果要保留3位小数,将表达式中的100改为1000即可,其余类推。

再比如,利用数据表中隐藏的自动编号列“_Identify”,可以生成指定格式的编号列。假如希望编号列能够按照下面的格式显示:

MP0001

MP0002

MP0011

MP0012

MP0123

MP0124

MP9998

MP9999

这种编码的规则是:前面是两个字母“MP”,后接4个数字,当数字的长度没有达到4位时要自动补充0。实现此目的的重点在于如何根据“_Identify”的值来确定补充多少个0:当“_Identify”的值是一位数时,要加3个0;是两位数时,加2个0;是3位数时加1个0。由此可见,需要补充的0的数量为:用4减去“_Identify”值的长度。用代码表示为:

4 - Len(Convert([_Identify],’System.String’))。

把这个长度用到Substring函数中,就表示要从字符串“0000”中截取的长度,也就是需补充多少个0:

Substring(‘0000’, 1, 4 - Len(Convert([_Identify],’System.String’)))

然后再在其前面加上字符串“MP”、在尾部加上“_Identify”列的值即可:

'MP'+ Substring('0000', 1, 4 - Len(Convert([_Identify],'System.String'))) + Convert([_Identify], 'System.String')

运行效果如下图所示。

如果要将编码的数字固定为6位,可将上述表达式中的“0000”改为“000000”,将4改为6。因而这种自动编号的表达式是可以通用的。

需要注意的是,当在列表达式中使用Convert函数进行数据类型转换时,日期型的数据只能与字符串进行转换,逻辑型数据只能与字符串及整数类型(Byte、Int16、Int32、Int64)进行转换。

1.4.4 表达式列和数据列的相互转换

单击【数据表】功能区的【列相关】功能组中的【其他】按钮,将弹出表达式列与数据列相互转换的下拉菜单,如下图所示。

❶ 当数据列转换为表达式列时,该数据列中的原有内容将全部消失,因为表达式列只能通过设置的表达式来动态生成数据。

❷ 同理,当表达式列转换为数据列时,由于该列的表达式已经被删除,因此数据一样会消失。既然已经转换为数据列,就只能自行编辑输入数据。

不论是数据列转换为表达式列,还是表达式列转换为数据列,一旦确定转换,Foxtable都会自动重新打开当前项目。