mysql - Table1 Left Join Table2 Minus(加入数据)

Question

表 1) m_conservationsetting

FacilityId   Unit   CategoryId
    1          1        1
    1          1        2
    1          1        3
    1          2        1
    1          2        2
    2          1        1
    2          2        1

唯一键(FacilityId 单元 CategoryId)

表 2) l_maintelog

FacilityId   Unit  CategoryId  Status
    1          1        1         0
    1          1        2         1
    1          1        3         0
    1          2        1         0
    2          1        1         0
    2          2        1         0

结果:

FacilityId   Unit   CategoryId
    1          2        2   

Table1 需要与 Table2 保持连接，它应该忽略连接结果并仅显示 table1 数据作为结果。Table1 LeftJoin Table2 -(加入数据)用于以下查询。得到结果的条件是查表2中的记录status=0

SELECT cs.FacilityId,Cs.Unit,cs.CategoryId 
FROM m_conservationsetting cs 
LEFT JOIN l_maintelog ml 
ON cs.FacilityId=ml.FacilityId and cs.Unit=ml.Unit
WHERE ml.Status=0 
GROUP BY cs.CategoryId

Answer 1

如果你只想获取那些不在left join结果中的记录，那么这样做:

SELECT t.* FROM m_conservationsetting AS t
WHERE NOT EXISTS (
    SELECT cs.FacilityId,Cs.Unit,cs.CategoryId 
    FROM m_conservationsetting AS cs 
    LEFT JOIN l_maintelog ml on
       (cs.FacilityId=ml.FacilityId and cs.Unit=ml.Unit)
    WHERE ml.Status=0 
    group by cs.CategoryId
)