从第一个表获取所有数据，即使联接表中不存在联接项

2024-07-27 • 问答

我有四个表Ledger，accMaster，Accd etail，accGroup。

CREATE TABLE Accdetail (DetailID int,MasterID int,LedgerID int,Credit decimal(18,2));
INSERT INTO Accdetail
VALUES (1,1,500),(2,2,1000);

CREATE TABLE Ledgers (ID int,Name varchar(10),accGroupID int);
INSERT INTO Ledgers
VALUES (1,'A',1),'B',1);

CREATE TABLE accMaster (MasterID int,Date date);
INSERT INTO accMaster
VALUES (1,'2019-11-04'),'2019-11-03');

CREATE TABLE accGroup (ID int,Name varchar(10));
INSERT INTO accGroup
VALUES (1,'accounts'),'Others');

SELECT AL.Name,SUM(AD.Credit)
FROM Accdetail AD
     LEFT OUTER JOIN Ledgers AL ON AL.ID = AD.LedgerID
     LEFT OUTER JOIN accMaster AM ON AM.MasterID = AD.MasterID
     LEFT OUTER JOIN accGroup AG ON AG.ID = AL.accGroupID
WHERE AM.Date = '2019-11-04'
GROUP BY AL.Name;

即使Ledger表中有两个条目，我也只能得到Ledger表中的一个条目的结果。这是因为在其他表中没有该分类账的相应条目。我发现有左联接和右联接的许多答案，但按照我的意愿却无济于事。

即使其他分类表中没有该分类帐的数据，我也希望从分类帐表中获取所有条目。

我想要得到的结果

| Name  | Credit| 
-----------------
| A     | 500   |
-----------------
| B     |  0    |
-----------------

SQL Fiddle

MS SQL Server 2017架构设置：

CREATE TABLE AccDetail (DetailID int,MasterID int,LedgerID int,Credit decimal(18,2));
INSERT INTO AccDetail
VALUES (1,1,500);

CREATE TABLE Ledgers (ID int,Name varchar(10),AccGroupID int);
INSERT INTO Ledgers
VALUES (1,'A',1),(2,'B',1);

CREATE TABLE AccMaster (MasterID int,Date date);
INSERT INTO AccMaster
VALUES (1,'2019-11-04'),'2019-11-03');

CREATE TABLE AccGroup (ID int,Name varchar(10));
INSERT INTO AccGroup
VALUES (1,'Accounts'),'Others');

查询1 ：

SELECT AL.Name,ISNULL(SUM(AD.Credit),0) AS SumOfCredits
FROM  Ledgers AL 
     LEFT OUTER JOIN AccDetail AD ON AL.ID = AD.LedgerID
     LEFT OUTER JOIN AccMaster AM ON AM.MasterID = AD.MasterID
     LEFT OUTER JOIN AccGroup AG ON AG.ID = AL.AccGroupID
     WHERE AM.Date = '2019-11-04' OR AM.Date IS NULL
GROUP BY AL.Name

Results ：

| Name | SumOfCredits |
|------|--------------|
|    A |          500 |
|    B |            0 |

SELECT Al.Name,SUM(ISNULL(AD.Credit,0))
FROM Ledgers AL
    LEFT OUTER JOIN AccDetail AD ON AL.Id = AD.LedgerID
    LEFT OUTER JOIN AccMaster AM ON AM.MasterID = AD.MasterID
    LEFT OUTER JOIN AccGroup AG ON AG.ID = AL.AccGroupID
WHERE AM.Date = '2019-11-04' OR AM.Date IS NULL 
GROUP BY Al.Name

从第一个表获取所有数据，即使联接表中不存在联接项

czhwan 回答：从第一个表获取所有数据，即使联接表中不存在联接项

大家都在问