查询表生成器

SQLGroupTableBuilder和SQLCrossTableBuilder可以从多个相关表中提取数据进行统计。
有的时候,我们并不想进行统计,只是希望从多个表中提取相关列组合成一个查询表而已。
当然我们可以用Select语句完成任务,但是通晓SQL语句的用户毕竟是少数,所以这样的任务这对于多数人来说,可不那么容易实现。

Foxtablet提供了一个查询表生成器:SQLJoinTableBuilder,无需编写任何SQL语句,即可从多个表中提取相关列组合成一个查询表。

定义一个SQLJoinTableBuilder的语法如下: 

New SQLJoinTableBuilder(TableName, BaseTable)

参数说明:

既然要从多个表中提取数据,除基表之外,我们还需要添加关联表。

添加关联表的语法为:

AddTable(LeftTable ,LeftCol, RightTable, RightCol)

LeftTable: 基表,或者已经添加的数据表 ,称之为左表。
LeftCol:   左表通过此列和右表关联。
RightTable:要添加的关联表,称之为右表。
RightCol:  右表通过此列和左表关联。

提示:

1、左表(LeftTable)和右表(Table2)并非一定要事先建立关联,只需两者的数据可以通过指定的列关联起来即可。
3、如果两个表存在一对多的父子关系,那么建议将多方(子表)作为左表,一方(父表)作为右表。
3、不管是基表,还是关联表,都可以是未加载的数据表,只要数据源存在此表即可。

例如:

Dim jb As New SQLJoinTableBuilder("统计表1","订单")
jb.AddTable(
"订单","产品ID","产品","产品ID")

表示基表是订单,此外添加产品表参与查询,产品表和订单表通过产品ID列关联起来。
由于产品表和订单表是一对多的父子关系,所以用AddTable添加表的时候,订单表(子表)作为左表,产品表(父表)作为右表。

如果要根据多列关联,请参考:

Dim jb As New SQLJoinTableBuilder("统计表1","产品")
Dim
Cols1() As String = {"型号","规格"}
Dim
Cols2() As String = {"型号","规格"}
jb.AddTable(
"产品",Cols1,"订单"
,Cols2)

上面的代码,产品表作为基表,添加订单表参与统计,两个表之间通过型号、规格两列关联起来。

添加了数据表之后,还要用AddCols方法来给查询表添加列,AddCols的语法为:

AddCols(Col1, Col2, Col3...)

Col1,Col2,Col3:要添加的多列。

不管列是来自于基表,还是来自关联表,都需要用AddCols方法加入。

如果存在同名列,添加列的时候,必须指定列的来源表,例如

Dim jb As New SQLJoinTableBuilder("查询表1","订单")
jb.AddTable(
"订单","产品ID","产品","产品ID")
jb.AddCols("{订单}.产品ID","产品名称","数量","单价")

由于产品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一多,我就会头晕。


本页地址:http://www.foxtable.com/webhelp/topics/2314.htm