SQL Server 离开加入 [英] SQL Server left joining

问题描述

我正在尝试在一个查询中进行左连接，但似乎我在某处错了.

I'm trying to make left join in one query, but it seems that I'm wrong somewhere.

table machines
-------------- 
machineID
FaNo
Barcode
RoutingCode
Name


table log
-------------
logID
lineBarcode
machineBarcode

在日志表中有关于机器和线路的记录.在一条线上可以有许多不同的机器和同一类型的机器.
机器类型是routingCode，所以我有兴趣选择该行中的所有机器并将它们分组.只有具有不同routingCode的机器才应该单独显示，我想得到每种类型机器的数量.
这是通过这种方式完成的.

In the log table there are records on the machines and the lines. On one line there can be many different machines and machines from the same type.
The machine type is routingCode, so I'm interested in selecting all the machines in the line and group them. Only machines with different routingCode should display separately, and I want to get the count of the machines of every type.
This is done this way.

SELECT routingcode, name, count(1)
FROM machines 
JOIN log ON log.machinebarcode = machines.barcode
WHERE log.linebarcode = 100000000001
GROUP BY routingcode, name

好的，一切运行顺利，但这样我只能得到log表中相关的机器，并根据linebarcode进行记录.
我认为如果我 LEFT JOIN 日志表，我将从 machines 表中获取所有机器并显示它们，当然只有在 log 中找到的机器 表将有适当的 count，但没有.
我哪里出错了，如何找到合适的解决方法?

Okay everything runs smoothly, but this way I get only machines which are related in log table and have record according to linebarcode.
I thinked that if I LEFT JOIN the log table I will get all the machines from the machines table and display them and of course only machines which are found in log table will have proper count, but no.
Where am I mistaking and how to find a proper workaround?

推荐答案

您需要将 log 上的条件放入 on 子句而不是 where.左外连接保留的非匹配行将为 log 中的所有列进行空扩展.

You need to put the condition on log into the on clause not the where. Non matching rows preserved by the left outer join will be null extended for all columns in log.

如果条件在 where 中，log.linebarcode 的 NULL 行将被再次删除.

Rows with NULL for log.linebarcode will be removed again if the condition is in the where.

此外，您需要计算 log 中不会是 NULL

Also instead of COUNT(1) you need to count a column from log that won't be NULL

SELECT routingcode,
       name,
       count(log.linebarcode)
FROM   machines
       LEFT JOIN log
         ON log.machinebarcode = machines.barcode
            AND log.linebarcode = 100000000001
GROUP  BY routingcode,
          name 

这篇关于SQL Server 离开加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！