mysql - SQL用条件区分列

我正在处理一个查询，当列CarId不为null时，我需要计算不同的LocationId行，如果它的CarId或null却得到所有0，但是我尝试过的查询将所有CarId，即使其为空

@LocId int

    Select Count(distinct a.CarId) from VehicleDetails a
        inner join VehicleDocuments b on a.DocId=b.DocId
        left join VehicleShipmentDetails dpg on dpg.VehicleShipmentId= b.VehicleShipmentId
        where b.LogicalDelete=0 and a.LogicalDelete=0
        and (dpg.LocationId= @LocId or dpg.LocationId= 0 or dpg.LocationId is null)



|  ID  |    CarId       |    LocationId   |  DateCreated  |
|------+----------------+-----------------+---------------|
|   1  |       1        |        5        |   02/03/2019  |
|   2  |       2        |      null       |   01/14/2019  |
|   3  |       2        |        0        |   02/03/2019  |
|   4  |       2        |        5        |   12/30/2018  |
|   5  |       4        |        3        |   01/10/2019  |
|   6  |       3        |        5        |   02/14/2019  |
|   7  |       2        |        5        |   03/13/2019  |

所需的输出：

|  ID  |    CarId       |    LocationId   |  DateCreated  |
+------+----------------+-----------------+---------------+
|   1  |       1        |        5        |   02/03/2019  |
|   2  |       2        |      null       |   01/14/2019  |
|   3  |       2        |        0        |   02/03/2019  |
|   4  |       2        |        5        |   03/13/2019  |
|   5  |       4        |        3        |   01/10/2019  |
|   6  |       3        |        5        |   02/14/2019  |

电流输出

|  ID  |    CarId       |    LocationId   |  DateCreated  |
+------+----------------+-----------------+---------------+
|   1  |       1        |        5        |   02/03/2019  |
|   2  |       2        |        5        |   01/14/2019  |
|   3  |       4        |        3        |   01/10/2019  |
|   4  |       3        |        5        |   02/14/2019  |

我得到一个4的计数，但我需要以6作为计数

编辑：我的目标是，如果CarId的值是LocationId或Null，但是要删除行以区分0，但是在我的当前代码上，它区分所有的CarId，即null，0和等于@LocId

最佳答案

您可以查询类似的内容，用实际的数据集替换your_table。

SELECT ID, CardId, LocationId, DateCreated
FROM your_table as T
WHERE NOT EXISTS (SELECT *
                  FROM your_table as T1
                  WHERE T.ID > T1.ID AND T.CarID = T1.CarID)