两句SQL语句的对比,不知道为什么错了
这个是第一个语句:
SELECT * from T_CarInfo
where T_CarInfo.CarID not in (
SELECT
T_RepairBill.CarID
FROM
T_RepairBillRepairType
LEFT JOIN T_RepairBill
ON T_RepairBillRepairType.RepairBillCode = T_RepairBill.RepairBillCode AND T_RepairBillRepairType.PointCode = T_RepairBill.PointCode
LEFT JOIN T_ComParameter
ON T_RepairBillRepairType.RepairType=T_ComParameter.ParameterFlag and T_RepairBillRepairType.PointCode=T_ComParameter.PointCode
where T_ComParameter.MaintenanceFlag=1
AND
(T_RepairBillRepairType.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection('0', 1, 1, -1 * ISNULL(0, 0), 1)))
)
and (T_CarInfo.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection('0', 1, 1, -1 * ISNULL(0, 0), 1)))
这个是第二个语句:
SELECT * from T_CarInfo
where NOT exists(
SELECT
1
FROM
T_RepairBillRepairType
LEFT JOIN T_RepairBill
ON T_RepairBillRepairType.RepairBillCode = T_RepairBill.RepairBillCode AND T_RepairBillRepairType.PointCode = T_RepairBill.PointCode
LEFT JOIN T_ComParameter
ON T_RepairBillRepairType.RepairType=T_ComParameter.ParameterFlag and T_RepairBillRepairType.PointCode=T_ComParameter.PointCode
where T_ComParameter.MaintenanceFlag=1
AND T_RepairBill.CarID=T_CarInfo.CarID
AND
(T_RepairBillRepairType.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection('0', 1, 1, -1 * ISNULL(0, 0), 1)))
)
and (T_CarInfo.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection('0', 1, 1, -1 * ISNULL(0, 0), 1)))
查询出来的结果无疑第二句是正确的,第一句是空的。
我不明白的是第一个语句与第二个语句所要表达的意义是一样的,都是排除T_ComParameter.MaintenanceFlag=1
这种情况下的数据信息。为什么第一个语句查询的是空的呢?求高手解答
------解决方案--------------------T_RepairBill.CarID
里有null值存在是导致查不到结果的原因
------解决方案--------------------这样应该ok的
SELECT * from T_CarInfo
where T_CarInfo.CarID not in (
SELECT
T_RepairBill.CarID
FROM
T_RepairBillRepairType
LEFT JOIN T_RepairBill
ON T_RepairBillRepairType.RepairBillCode =