mysql - LeftJoin 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单位类别Id)

表 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      |  1   |     2      |
|     1      |  2   |     2      |
+------------+------+------------+

Table1 需要与 Table2 保持连接，并且应忽略连接结果并仅显示 table1 数据作为结果。
Table1 LeftJoin Table2 -(连接数据)用于以下查询。获取结果的条件是检查Table2中记录的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

您可以在第二个表的键列上使用 NULL 条件来消除两个表中都存在的结果

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
and ml.FacilityId IS NULL) 
group by cs.CategoryId