时间戳

首页 > 解决方案 > 如何使用连接显示不匹配的结果（sqlserver）

sql-server - 如何使用连接显示不匹配的结果（sqlserver）

问题描述

我正在尝试这段代码

SELECT  e.codartp AS ARTICLE,e.codartc AS COMPONENTS, sa.CODALM as WAREHOUSE, 
sa.unidades as UNITS, sa.UBICACION as 'LOCATION' FROM escandallo e 
left join STOCKALM sa on e.codartc = sa.codart  
where trim(e.codartp) = '30393' and sa.CODALM = 0

它显示如下

ARTICLE COMPONENTS  WAREHOUSE   UNITS   LOCATION
  30393     310        0         376    XHY40081
  30393     C042       0         302    NULL
  30393     C042       0         1158   WCY40082

但我希望它也显示不匹配的结果。我知道那篇文章还有另一个组件，但它没有显示，因为 table stockalm 不包括库存为 0 的项目。

即使在连接表中找不到每个组件，是否也可以显示它？

非常感谢

标签： sql-serverleft-join

解决方案

使用ON子句中的条件而不是Where子句

SELECT  e.codartp AS ARTICLE,e.codartc AS COMPONENTS, sa.CODALM as WAREHOUSE, 
sa.unidades as UNITS, sa.UBICACION as 'LOCATION' FROM escandallo e 
left join STOCKALM sa on e.codartc = sa.codart AND trim(e.codartp) = '30393' and sa.CODALM = 0

另外，看看这个：

现在，此查询过滤从表 (escandallo) "trim(e.codartp) = '30393'" 和表 "STOCKALM" 中满足的所有记录为 "NULL" 显示条件不匹配

SELECT  e.codartp AS ARTICLE,e.codartc AS COMPONENTS, sa.CODALM as WAREHOUSE, 
sa.unidades as UNITS, sa.UBICACION as 'LOCATION' FROM escandallo e 
left join STOCKALM sa on e.codartc = sa.codart and sa.CODALM = 0
where trim(e.codartp) = '30393'

推荐阅读