合并多个查询结果
有的时候,我们会有将多个查询结果组合在一起的需求。
组合同一数据源的查询结果
SQLJoinTableBuilder有一个Union方法,可以组合多个其他SQLJoinTableBuilder的查询结果。
语法:
Union(Builder, All)
Builder:被组合的另一个SQLJoinTableBuilder
All: 可选参数,组合的时候默认是排除重复值的,如果需要组合所有数据,请将此参数设置为True
参与组合的多个SQLJoinTableBuilder,其生成的查询表必须具备相同的结构。
示例
假设你有订单2009、订单2010、订单2011三个表,需要从这三个订单表中找出所有客户为“红阳事业”的订单:
Dim
jb1
As New
SQLJoinTableBuilder("查询表1","订单2009")
jb1.ConnectionName = "Sale"
jb1.AddTable("订单","产品ID","产品","产品ID")
jb1.AddTable("订单",
"客户ID","客户","客户ID")
jb1.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb1.AddExp("金额","数量
* 单价")
jb1.Filter = "客户名称 = '红阳事业'"
Dim jb2
As New
SQLJoinTableBuilder("查询表1","订单2010")
jb2.ConnectionName = "Sale"
jb2.AddTable("订单","产品ID","产品","产品ID")
jb2.AddTable("订单",
"客户ID","客户","客户ID")
jb2.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb2.AddExp("金额","数量
* 单价")
jb2.Filter = "客户名称 = '红阳事业'"
Dim jb3
As New
SQLJoinTableBuilder("查询表1","订单2011")
jb3.ConnectionName = "Sale"
jb3.AddTable("订单","产品ID","产品","产品ID")
jb3.AddTable("订单",
"客户ID","客户","客户ID")
jb3.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb3.AddExp("金额","数量
* 单价")
jb3.Filter = "客户名称 = '红阳事业'"
jb1.Union(jb2)
'组合jb2
jb1.Union(jb3)
'组合jb3
jb1.Build()
MainTable =
Tables("查询表1")
组合不同数据源的查询结果
如果查询结果来自于不同的数据源,需要采用Merge方法组合,Merge方法不如Union方法高效。
示例
假设你有三个数据源,分别为Sale1、Sale2、Sale3,三个数据源都有订单表,需要从这三个订单表中找出所有客户为“红阳事业”的订单:
Dim
jb1 As
New SQLJoinTableBuilder("查询表1","订单")
jb1.ConnectionName = "Sale1"
jb1.AddTable("订单","产品ID","产品","产品ID")
jb1.AddTable("订单",
"客户ID","客户","客户ID")
jb1.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb1.AddExp("金额","数量
* 单价")
jb1.Filter = "客户名称 = '红阳事业'"
Dim
jb2 As
New SQLJoinTableBuilder("查询表1","订单")
jb2.ConnectionName = "Sale2"
jb2.AddTable("订单","产品ID","产品","产品ID")
jb2.AddTable("订单",
"客户ID","客户","客户ID")
jb2.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb2.AddExp("金额","数量
* 单价")
jb2.Filter = "客户名称 = '红阳事业'"
Dim
jb3 As
New SQLJoinTableBuilder("查询表1","订单")
jb3.ConnectionName = "Sale3"
jb3.AddTable("订单","产品ID","产品","产品ID")
jb3.AddTable("订单",
"客户ID","客户","客户ID")
jb3.AddCols("产品名称","客户名称","日期",
"数量","单价")
jb3.AddExp("金额","数量
* 单价")
jb3.Filter = "客户名称 = '红阳事业'"
jb1.Merge(jb2)
'组合jb2
jb1.Merge(jb3) '组合jb3
jb1.Build()
MainTable
= Tables("查询表1")