我有一个名为Updated_SQLSOLD
的表,字段名称为Branch
,Barcode
。我有超过 10k 个分支机构的唯一条形码,但并非所有条形码都注册到所有分支机构中。但是即使我只找到 10 个分支的条形码,我也需要显示 7 个分支。
例。
[Branch] [Barcode]
----------------------
branch1 | 25122225
branch2 | 25122225
branch3 | 25122225
branch4 | 25122225
branch5 | 33333333
branch6 | 33333333
branch7 | 66666666
branch8 | 25122225
branch9 | 56565656
branch10 | 56565656
branch1 | 55444444
branch1 | 56565656
branch2 | 56565656
branch3 | 56565656
SELECT [Branch], [Barcode]
FROM [MHO_Report].[dbo].[Updated_SQLSOLD]
where [Barcode] = '25122225'
结果是:
[Branch] [Barcode]
----------------------
branch1 | 25122225
branch2 | 25122225
branch3 | 25122225
branch4 | 25122225
branch8 | 25122225
现在如何显示所有分支,即使没有可用的序列,如下所示:
[Branch] [Barcode]
----------------------
branch1 | 25122225
branch2 | 25122225
branch3 | 25122225
branch4 | 25122225
branch5 | Null
branch6 | Null
branch7 | Null
branch8 | 25122225
branch9 | Null
branch10 | Null
您可以将所有可用的分支放在子查询中并使用它。
SELECT tbBranch.Branch, BarCode
FROM
(
SELECT DISTINCT Branch
FROM [MHO_Report].[dbo].[Updated_SQLSOLD]
)tbBranch
LEFT OUTER JOIN [MHO_Report].[dbo].[Updated_SQLSOLD] A
ON A.Branch = tbBranch.Branch
AND A.BarCode = '25122225'
您可以尝试使用左联接
SELECT a.[Branch], a1.[Barcode]
FROM [MHO_Report].[dbo].[Updated_SQLSOLD] a
LEFT JOIN [MHO_Report].[dbo].[Updated_SQLSOLD] a1
ON a.[Branch]=a1.[Branch] AND a.[Barcode] = '25122225'
您可以使用聚合:
SELECT us.Branch,
MAX(CASE WHEN us.Barcode = '25122225' THEN us.Barcode END) as Barcode
FROM [MHO_Report].[dbo].Updated_SQLSOLD us
GROUP BY Barcode;
当您有一个单独的分支列表时,我会推荐一个JOIN
:
SELECT b.Branch, MAX(us.Barcode) as Barcode
FROM branches b LEFT JOIN
[MHO_Report].[dbo].[Updated_SQLSOLD] us
ON us.branch = b.branch AND us.Barcode = '25122225'
GROUP BY b.branch;