SQL Server 实战 - 多种连接

背景

一、多种连接

1. 复合连接条件

2. 跨数据库连接

3. 隐连接

4. 自连接

5. 多表外连接

6. UNION ALL

二、一个对比例子

背景

本专栏文章以 SAP 实施顾问在实施项目中需要掌握的 sql 语句为偏向进行选题：

用例：SAP B1 的数据库
工具：SQL Server。

本文将详细讲解多种连接的复杂查询，并附带实战例题及代码解法。

前文：《SQL Server 查询设置 - LIKE/DISTINCT/HAVING/排序》

一、多种连接

1. 复合连接条件

查询时，通过 AND/OR 连接条件对查询进行筛选，很常见的基本用法，这里只展示语法：

SELECT [列名]
FROM [表名]
WHERE [条件1] AND/OR [条件2]

2. 跨数据库连接

有时候会需要使用其他账套的信息，在数据库层面看就是需要跨数据库连接表信息，比如同一家公司设立了多个账套，需要在账套间对账时。用 USE 即可调用其他数据库。

跨数据库连接语法（这里的[]在替换内容时需要保留，详见下面例子）：

USE [数据库1]
SELECT * FROM [数据库2].[dbo].[表名] --注意：这里的[]在替换内容时需要保留，详见下面例子

例：在使用数据库 DTWDATEBASE1 时，调用数据库 SBODemoCN 的业务伙伴主数据表单。

USE DTWDATEBASE1SELECT CardCode
FROM [SBODemoCN].[dbo].[OCRD]

3. 隐连接

就是不那么明显地连接，不想内连接和外连接，有直接和明显的 JOIN，LEFT JOIN，RIGHT JOIN。

隐连接语法：

SELECT *
FROM [表1],[表2]
WHERE [条件1]

从效果上来说其实和内连接一样，内连接语法形式：

SELECT *
FROM [表1]
JOIN [表2] ON [条件1]

例子：

--隐连接
SELECT *
FROM OCRD T0,ORDR T1
WHERE T0.CardCode=T1.CardCode--内连接
SELECT *
FROM OCRD T0
JOIN ORDR T1
ON T0.CardCode=T1.CardCode

4. 自连接

顾名思义，即自我连接，和正常的表连接一样，分为自内连接（JOIN）和自外连接（LEFT/RIGHT JOIN）。一般的内连接和外连接都是连接两张不同的表，都是有时候需要连接相同的表。从语法上与一般连接无差别，仅是将同一张表引用两次，赋值为不同的表格。

例：显示科目表的上级目录名称。

如下图，科目表的系统表单内容如下，有且仅有当前目录的编码和名称，以及上级目录的编码，此时要达到例题效果则需要使用自连接。

SELECT T0.AcctCode as '科目编号',T0.AcctName as '科目名称',T1.AcctName as '上级科目名称'
FROM OACT T0
LEFT JOIN OACT T1
ON T0.FatherNum = T1.AcctCode

查询结果如下：

5. 多表外连接

和正常的用 LEFT JOIN，RIGHT JOIN 连接的外连接一样，只不过多加几行。

例：按时间段查询科目总额汇总表，包括科目信息、时间范围、期初余额、借方发生额、贷方发生额、期末余额

在此问题中需要用到【日记账分录主表】、【日记账分录子表】、【科目表】。

DECLARE @BeginDate DateTime,@EndDate DateTime
SET @BeginDate=/* FROM JDT1 T0 WHERE T0.RefDate <= */ '[%0]'
SET @EndDate=/* FROM JDT1 T0 WHERE T0.RefDate >= */ '[%1]'SELECT 
T2.FatherNum AS '科目类别',T2.AcctCode AS '科目编号',T2.AcctName AS '科目名称'
,CAST(@BeginDate AS nvarchar(20)) +'~'+CAST(@EndDate AS nvarchar(20)) 日期范围
,SUM(CASE WHEN T0.RefDate<@BeginDate THEN (T1.Debit-T1.Credit) ELSE 0 END) AS '期初余额'
,SUM(CASE WHEN T0.RefDate BETWEEN @BeginDate AND @EndDate THEN T1.Debit ELSE 0 END) AS '借方发生额'
,SUM(CASE WHEN T0.RefDate BETWEEN @BeginDate AND @EndDate THEN T1.Credit ELSE 0 END) AS '贷方发生额'
,SUM(CASE WHEN T0.RefDate<=@EndDate THEN (T1.Debit-T1.Credit) ELSE 0 END) AS '期末余额'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId=T1.TransId
INNER JOIN OACT T2 ON T1.Account=T2.AcctCode
WHERE T0.RefDate<=@EndDate
GROUP BY T2.FatherNum,T2.AcctCode,T2.AcctName

6. UNION ALL

在需要创造一个新列时，比如一部分用户需要填入内容 [A]，一部分需要填入内容 [B]，此时使用 UNION ALL 将两个结构完全一致的表格进行合并，实现上述需求。

例：制作一个销售出货单，查出客户编码、客户名称、物料编号、物料名称、出货的物料数量。

这里不能单纯地考虑出货单，需要进一步

SELECT T0.CardCode,T0.CardName,T0.DocDate,T0.DocNum,T1.ItemCode,T1.Dscription,T1.Quantity
FROM ODLN T0
INNER JOIN DLN1 T1 ON T0.DocEntry=T1.DocEntry
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]' AND T0.CardName = '[%2]'UNION ALLSELECT T0.CardCode,T0.CardName,T0.DocDate,T0.DocNum,T1.ItemCode,T1.Dscription,T1.Quantity*-1
FROM ORDN T0
INNER JOIN RDN1 T1 ON T0.DocEntry=T1.DocEntry
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]' AND T0.CardName = '[%2]'

查询管理器运行效果：

查询管理器的基本使用见《SAP B1 基础实操 - 查询管理器（基础版）》；代码中 sql server 并不支持的 SAP 查询特殊符号等见《SAP B1 查询管理器 - 新建类别/专用sql语法》

二、一个对比例子

要求：查找买了物料 A00001 的客户的详细信息，包含客户编号、客户名称、电话号码、联系人。

方法一：多表外连接

SELECT DISTINCT T0.CardCode,T0.CardName,T0.CntctPrsn,T0.Phone1
FROM OCRD T0
LEFT JOIN ORDR T1 ON T1.CardCode=T0.CardCode --多表外连接
LEFT JOIN RDR1 T2 ON T1.DocEntry=T2.DocEntry 
WHERE T2.ItemCode = 'A00001'

方法二：子查询

其中会涉及到的子查询见同专栏文章《SQL Server 查询设置 - LIKE/DISTINCT/HAVING/排序》中 四-3 部分

SELECT CardCode,CardName,CntctPrsn,Phone1
FROM OCRD
WHERE CardCode IN (SELECT CardCode -- 子查询
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry=T1.DocEntry
WHERE T1.ItemCode = 'A00001')

查询结果一致。