神奇的SQL语言——多表联合操作

wsri

多表联合操作
对于EXCEL里的SQL操作，论坛里已经有很多的论述和例子，这里我就不讲什么原理和基础了，主要是讲讲自己的操作心得和大家分享。
EXCEL里可以根据自己的需要添加很多的表，而SQL对多表之间的操作支持的非常好，我们可以利用SQL语言对多表进行合并和筛选，按照我们需要的格式和条件取得数据。这里我们主要介绍INNER JOIN 运算。
INNER JOIN 运算       
组合两个表中的记录，只要在公共字段之中有相符的值。
语法
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
INNER JOIN 运算可分为以下几个部分：
部分        说明
table1, table2        记录被组合的表的名称。
field1, field2        被联接的字段的名称。若它们不是由数字构成的，则这些字段必须为相同的数据类型并包含同类数据，但它们无须具有相同的名称。
compopr        任何的关系比较运算子："=," "<," ">," "<=," ">=," 或 "<>."


闲话少说，我们直接从例子说起：
一个工作簿里有两个表，一个是基础数据，里面是所有学生的自然状况。另一个是成绩表，里面是每个学生的三面功课成绩（表格见附件）。
我们的程序：
Public Sub DBCZ1()

Set rngt = Sheets("基础数据").Range("A1").CurrentRegion
        JCSJ = rngt.Address(0, 0)

Set rngt = Sheets("成绩").Range("A1").CurrentRegion
        CJ = rngt.Address(0, 0)
        
        
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
  
    strSQL = "SELECT A.*, B.* FROM [基础数据$" & JCSJ & "] AS A LEFT JOIN [成绩$" & CJ & "] AS B ON A.学号=B.学号"

  rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
For Each Field In rs.Fields

[A1].Offset(0, i) = Field.Name

i = i + 1
Next

Sheets("汇总").Range("A2").CopyFromRecordset rs
Set rs = Nothing
Set cnn = Nothing


End Sub

关键语句是：
strSQL = "SELECT A.*, B.* FROM [基础数据$" & JCSJ & "] AS A LEFT JOIN [成绩$" & CJ & "] AS B ON A.学号=B.学号"
我们就得到了两个表里的所有列项目，那么我们先来学习一下INNER JOIN 运算。
MicrosoftJetSQL语言参考.chm里的INNER JOIN 运算说明很简单，我结合网上的一个教程给大家详细说明一下。
两个表：一个基础数据，里面有学号、姓名等自然状况，我们称为A表；另一个表是成绩，有学号、姓名、各科成绩，我们称为B表。
有SQL基本知识的人都知道，两个表要做连接，就必须有个连接字段，从上表中的数据可以看出，在A表中的学号和B表中的学号就是两个连接字段。
下图说明了各表之间的关系：





现在我们对内连接和外连接一一讲解。
1.内连接：利用内连接可获取两表的公共部分的记录，即记录集C部分。
语句如下：
strSQL = "SELECT A.*, B.* FROM [基础数据$" & JCSJ & "] AS A  INNER JOIN  [成绩$" & CJ & "] AS B ON A.学号=B.学号"
A表的011学号没有被带入，因为B表里没有他的成绩，不是公共部分。B表的012也没有带入，因为A表里没有他的资料，也不是公共部分。
这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。


2.外连接：外连接分为两种，一种是左连接（Left JOIN）和右连接（Right JOIN）
(1)左连接（Left JOIN）：即图公共部分记录集C＋表A记录集A1。     
     语句如下：
strSQL = "SELECT A.*, B.* FROM [基础数据$" & JCSJ & "] AS A  Left  JOIN  [成绩$" & CJ & "] AS B ON A.学号=B.学号"
这样就是以A表的内容为主，A表的011学号被带入，B表的012没有带入，因为A表里没有他的资料，所以没带入。

   在语句中，A在B的左边，并且是Left Join，所以其运算方式为：A左连接B的记录=图公共部分记录集C＋表A记录集A1
           在图中即记录集C中的存在的学号为：1 、2、3、4、5、6、7、8、9、10           
        表A所有记录集A中存在的学号为：1 2 3 4 5 6 7 8 9 10 11
     表A记录集A1中存在的学号=(即A表中所有学号)-(图中记录集C中存在的学号)，最终得出为：11
           由此得出图中A左连接B的记录=图公共部分记录集C＋表A记录集A1,
           最终得出的结果中可以看出实际就是以A表的内容为主，A表的011学号被带入，B表的012没有带入，因为A表里没有他的资料，所以没带入。


(2)右连接（Right JOIN）：即图3公共部分记录集C＋表B记录集B1。
     语句如下：
strSQL = "SELECT A.*, B.* FROM [基础数据$" & JCSJ & "] AS A  Right  JOIN  [成绩$" & CJ & "] AS B ON A.学号=B.学号"

在语句中，A在B的左边，并且是Right Join，所以其运算方式为：A右连接B的记录=图公共部分记录集C＋表B记录集B1
           在图中即记录集C中的存在的学号为：1 、2、3、4、5、6、7、8、9、10           
           表B所有记录集B中存在的学号为：1 2 3 4 5 6 7 8 9 10 12
           表B记录集B1中存在的学号=(即B表中所有学号)-(图中即记录集C中存在的学号)，最终得出为：12
           由此得出图中A右连接B的记录=图公共部分记录集C＋表B记录集B1,
           最终得出的结果中可以看出实际就是以B表的内容为主，B表的012学号被带入，A表的011没有带入，因为B表里没有他的资料，所以没带入。



其实对图3左右翻转一下就可以得出以下结论：
select * from B Left JOIN A ON A.学号=B. 学号
和select * from A Right JOIN B ON A. 学号=B. 学号所得出的记录集是一样的
而
select * from B Right JOIN A ON A. 学号=B. 学号
和select * from A Left JOIN B ON A. 学号=B. 学号所得出的记录集也是一样的。

连接五个数据表的用法：
    FROM ((((A INNER JOIN B ON A.name=B. name )
INNER JOIN C ON A.name=C. name )
INNER JOIN D ON A.name=D. name)
INNER JOIN E ON A.name=E. name)
    语法格式可以概括为：
    FROM  (((（表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)  INNER JOIN 表4 ON 表1.字段号=表4.字段号) INNER JOIN 表5 ON表 1.字段号=表5.字段号）

下表是我的一个实例表，但原表是在ACCESS里做的，现翻到EXCEL里。
    strSQL = "SELECT A.生产编号, A.批次号, A.采购订单号, A.物料编码, A.物料描述, IIf(IsNull(B.零件编码) Or B.零件编码="",A.物料编码,B.零件编码) AS 子件编码, " & _
             " IIf(IsNull(B.零件编码) Or B.零件编码="",A.物料描述,B.零件描述) AS 子件描述, A.订单数量, (B.组数*A.计划数量) AS 计划数量, A.交付日期 AS 结束日期, B.套别,  " & _
             "(B.组数*A.计划数量) AS 出产数量, A.用户, (A.生产编号 & B.套别) AS 识别码, B.说明, B.组数, B.钢种, B.标识, C.单重, C.单价90, C.单价09, B.序号, C.类别, A.备注 " & _
             "FROM ([主生产计划$" & ZSCJHAdd & "] AS A LEFT JOIN [BOM$" & BOMAdd & "] AS B ON A.物料编码=B.物料编码) LEFT JOIN [基础数据$" & JCSJAdd & "] AS C ON A.物料编码=C.物料编码"

里面对于字段有一些函数的处理，大家可以参考一下！
下次跟大家共同学习一下UNION 运算。

guzhen9315

好!    学习!,  谢谢!!

lcmphy

很有用,有空时一定好好学!先谢谢楼主!不懂之处再请教!

qdfd2009

detergent181

正要呢?来得很及时.....

xyfh

期待UNION，感谢！

hh168

那如果是要左连接多个表应如何处理呢？

hh168

哦 看到了


连接五个数据表的用法：
    FROM ((((A INNER JOIN B ON A.name=B. name )
INNER JOIN C ON A.name=C. name )
INNER JOIN D ON A.name=D. name)
INNER JOIN E ON A.name=E. name)

dhf327

非常好的内容

kitty8cat

学习了，谢谢楼主