查询表生成器
SQLGroupTableBuilder和SQLCrossTableBuilder可以从多个相关表中提取数据进行统计。
有的时候,我们并不想进行统计,只是希望从多个表中提取相关列组合成一个查询表而已。
当然我们可以用Select语句完成任务,但是通晓SQL语句的用户毕竟是少数,所以这样的任务这对于多数人来说,可不那么容易实现。
Foxtablet提供了一个查询表生成器:SQLJoinTableBuilder,无需编写任何SQL语句,即可从多个表中提取相关列组合成一个查询表。
定义一个SQLJoinTableBuilder的语法如下:
New SQLJoinTableBuilder(TableName, BaseTable)
参数说明:
TableName
字符型,指定即将生成的查询表的名称
BaseTable
字符型,指定用于生成查询表的第一个数据表,我们称之为基表
既然要从多个表中提取数据,除基表之外,我们还需要添加关联表。
添加关联表的语法为:
AddTable(LeftTable ,LeftCol, RightTable, RightCol)
LeftTable: 基表,或者已经添加的数据表
,称之为左表。
LeftCol: 左表通过此列和右表关联。
RightTable:要添加的关联表,称之为右表。
RightCol: 右表通过此列和左表关联。
提示:
1、左表(LeftTable)和右表(Table2)并非一定要事先建立关联,只需两者的数据可以通过指定的列关联起来即可。
3、如果两个表存在一对多的父子关系,那么建议将多方(子表)作为左表,一方(父表)作为右表。
3、不管是基表,还是关联表,都可以是未加载的数据表,只要数据源存在此表即可。
例如:
Dim j
b As New SQLJoinTableBuilder("统计表1","订单")表示基表是订单,此外添加产品表参与查询,产品表和订单表通过产品ID列关联起来。
由于产品表和订单表是一对多的父子关系,所以用AddTable添加表的时候,订单表(子表)作为左表,产品表(父表)作为右表。
如果要根据多列关联,请参考:
Dim
jb As New SQLJoinTableBuilder("统计表1","产品")上面的代码,产品表作为基表,添加订单表参与统计,两个表之间通过型号、规格两列关联起来。
添加了数据表之后,还要用AddCols方法来给查询表添加列,AddCols的语法为:
AddCols(Col1, Col2, Col3...)
Col1,Col2,Col3:要添加的多列。
不管列是来自于基表,还是来自关联表,都需要用AddCols方法加入。
如果存在同名列,添加列的时候,必须指定列的来源表,例如
Dim
jb As New SQLJoinTableBuilder("查询表1","订单")
由于产品ID在产品表和订单表都存在,所以必须指定其来源表,而产品名称只存在于产品表,数量和单价只存在于订单表,所以不需要指定来源表。
注意指定来源表的时候,表名必须用大括号括起来,例如:{订单}.产品ID
如果要将某个表所有列加入查询表,可以用这样的格式:{表名).*
例如:
Dim jb
As New
SQLJoinTableBuilder("查询表1","订单")
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddCols(
表示将产品表的产品名称列及订单表的所有列加入到查询表中。
我们还可以用AddExp方法添加表达式列,语法为:
AddExp(Name, Expression)
Name: 列名
Expression:表达式
例如:
jb.AddExp(
"金额", "数量 * 单价")表示给查询表添加一个名为“金额”的列,此列通过数量和单价两列计算得出,计算公式为:[数量] * [单价]
同样参与表达式计算的列,如果存在对多个表中,必须指定列的来源表。
例如,假定产品表和订单表都存在单价列,那么就必须如下设置金额的表达式:
jb.AddExp("金额", "数量 * {订单}.单价")
注意表名要用大括号括起来。
利用AddExp方法,可以间接实现给列改名的功能,例如:
jb.AddExp("品名", "产品名称")
表示添加一个名为“品名”的表达式列,其表达式为“产品名称”,这等于就是将“产品名称”列添加到查询表,并改名为“品名”。
SQLJoinTableBuilder的属性有:
ConnectionName: 指定数据源名称,如果省略,表示使用内部数据表。
Caption: 字符型,指定新生成查询表的标题。
Filter: 字符型,设置统计条件。
Order: 字符型,指定排序列。
Top:
整数型,指定要加载的行数。
Distinct: 逻辑型,是否排除重复值。
CommandTimeOut:
整数型,用于设置统计后台数据的超时时限,默认为30秒,一般不需要设置此属性,除非需要进行非常耗时超过30秒的统计,以至于出现超时错误,此时可适当地加大CommandTimeOut属性的值。
提示:
1、例如你有100万甚至更多的行,那么用SQLJoinTableBuilder生成查询表的唯一后果就是计算机崩溃,所以数据量大的时候,记得设置SQLJoinTableBuilder的Filter属性。
2、添加表达式列或设置统计条件时,具体语法和所采用的数据源有关,我们会在SQL章节对此进行具体介绍。
接下来我们来实战演练一下SQLJoinTableBuilder,请先打开CaseStudy目录下的文件:多表统计.Table。
这个文件的所有表都来自外部数据源,外部数据源的名称为Sale,外部数据源是一个Access文件:多表统计.mdb
整个系统包括三个表,分别是客户、产品和订单:
可以直接在命令窗口执行下面的示例代码。
示例一
Dim
jb
As New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddCols("产品名称",
"日期",
"数量","单价")
jb.Build()
MainTable = Tables("查询表1")
生成的查询表如下图,其中产品名称列来自于产品表,日期、数量和单价三列来自于订单表:
示例二
Dim
jb
As New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单",
"客户ID","客户","客户ID")
jb.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb.AddExp("金额","数量
* 单价")
jb.Build()
MainTable = Tables("查询表1")
生成的查询表如下图,产品名称列来自于产品表,客户名称列来自客户表,日期、数量、单价和金额来自于订单表,其中金额列是通过数量和单价计算得出的:
示例三
Dim
jb
As New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单",
"客户ID","客户","客户ID")
jb.AddExp("产品","产品名称")
jb.AddExp("客户","客户名称")
jb.AddCols("日期",
"数量","单价")
jb.AddExp("金额","数量
* 单价")
jb.Build()
MainTable = Tables("查询表1")
生成的查询表如下图,产品列由产品表的产品名称列改名得来,客户列由客户表的客户名称列改名得来,日期、数量、单价和金额来自于订单表,其中金额列是通过数量和单价计算得出的:
示例四
遇上同名列的时候,记得要指定来源表,例如下面的产品ID列:
Dim
jb As
New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddCols("{产品}.产品ID","产品名称",
"日期",
"数量","单价")
jb.Build()
MainTable
= Tables("查询表1")
注意表名要用大括号括起来。
示例五
在命令窗口执行下面的代码,可以得到所有订购过浓缩咖啡的客户的名单:
Dim
jb As
New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单",
"客户ID","客户","客户ID")
jb.AddCols("客户名称")
jb.Filter
= "产品名称 = '浓缩咖啡'"
jb.Distinct
= True
jb.Build()
MainTable =
Tables("查询表1")
代码的关键在于:
jb.Filter =
"产品名称 = '浓缩咖啡'"
jb.Distinct =
True
第一行代码设置了条件,第二行代码排除了重复值。
示例六
在命令窗口执行下面的代码,可得到订购数量最多的前10个订单:
Dim
jb As
New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单",
"客户ID","客户","客户ID")
jb.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb.AddExp("金额","数量
* 单价")
jb.Top = 10
jb.Order =
"数量 Desc"
jb.Build()
MainTable =
Tables("查询表1")
示例七
SQLJoinTableBuilder也可以进行简单的分组统计。
用AddCols增加列时,可以将最后一个参数设置为True,以表示添加的是分组列。
用AddExp增加表达式列时,可以将最后一个参数设置为True,以表示添加的是分组列。
例如按年统计各客户订购每种产品的数量和金额:
Dim
jb
As New
SQLJoinTableBuilder("查询表1","订单")
jb.ConnectionName
=
"Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单","客户ID","客户","客户ID")
jb.AddExp("年","Year(日期)",True)
jb.AddCols("产品名称","客户名称",True)
jb.AddExp("数量","Sum(数量)")
jb.AddExp("金额","Sum(数量
* 单价)")
jb.Build()
MainTable = Tables("查询表1")
得到的统计表为:
提示:
SQLJoinTableBuilder毕竟不是专门的统计工具,只能进行简单的分组统计,不能交叉统计,更不具备数据分析功能,我们建议数据统计用专门的统计工具,而数据查询用SQLJoinTableBuilder。
所以本示例只是让大家知道SQLJoinTableBuilder也具备统计功能而已,并无太多实质意义。
示例八
除Build方法外,SQLJoinTableBuilder还有一个BuildSQL方法,用于生成Select语句,例如在命令窗口执行:
Dim
jb
As New
SQLJoinTableBuilder("查询表1","订单")
Dim sl
As String
jb.ConnectionName
= "Sale"
jb.AddTable("订单","产品ID","产品","产品ID")
jb.AddTable("订单",
"客户ID","客户","客户ID")
jb.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb.AddExp("金额","数量
* 单价")
sl =
jb.BuildSQL()
Output.Show(sl)
可以看出生成的Select语句为:
Select 产品名称,客户名称,日期,数量,单价,数量 * 单价 As [金额] From ({订单} INNER JOIN {产品} ON {产品}.[产品ID] = {订单}.[产品ID]) INNER JOIN {客户} ON {客户}.[客户ID] = {订单}.[客户ID]
似乎直接用Select语句更为简洁点,但是用SQLJoinTableBuilder不需要任何SQL语言知识,而且是傻瓜式的编写,简单直接,一点不伤脑筋;我个人是很喜欢这个工具的,因为Select语句中的Join一多,我就会头晕。