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')

 查询结果一致。