当我在查询中添加where时,它现在显示count0

r1wp621o  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(326)

我有一个问题,查询没有显示 COUNT 添加where子句时为0。
我有两张“停车”和“主动停车”的table。我想得到 COUNT 活动停车场数量 id_parking 那个 datetime_out>= 然后与当前时间进行比较 parking_space .

Parking
| id_parking     | parking_name | parking_location | parking_space |
| -------------- | ------------ | ---------------- |  ------------ |
| 1              | ABC          |  1st street      | 35            |
| 1              | ABC          |  2nd street      | 50            |
| 1              | ABC          |  3rd street      | 150           |

Active Parking
| id_active_parking   |  id_parking | car_plates | datetime_IN          | 
| ------------------- | ----------- | ---------- | -------------------- |
| 1                   | 1           | 00-XXX-00  | 2018-12-16 14:00:00  |
| 2                   | 1           | 00-XXX-00  | 2018-12-16 14:21:34  |
| 3                   | 2           | 00-XXX-00  | 2018-12-16 21:35:00  |
| 4                   | 2           | 00-XXX-00  | 2018-12-16 22:00:00  |

| datetime_OUT         |
| -------------------- |
| 2018-12-16 15:00:00  |
| 2018-12-16 15:21:34  |
| 2018-12-16 22:35:00  |
| 2018-12-16 23:00:00  |

查询:

SELECT
  p.id_parking, 
  p.parking_name,
  p.parking_location,
  p.parking_space, 
  COUNT(ap.id_parking) AS Active
FROM
  parking p
  LEFT JOIN active_parking ap 
    ON ap.id_parking = p.id_parking
WHERE
  ap.datetime_OUT >= '2018-12-16 22:16:00'
GROUP BY p.id_parking

查询结果:

| id_parking     | COUNT(ap.id_parking) |
| -------------- | -------------------- |
| 2              | 2                    |

没有显示 id_parking 1和3 COUNT = 0

3zwtqj6y

3zwtqj6y1#

在这种情况下使用左连接的原因是什么?删除groupby+count并检查结果。我认为在这种情况下应该使用内部连接。你可以在很多地方看到差异,包括wiki
如果您确实需要p.id\停车场的所有列表,您应该将where子句移到:

LEFT JOIN active_parking ap ON (
        ap.id_parking = p.id_parking
    AND ap.datetime_OUT >= '2018-12-16 22:16:00'
)

那么你应该有适当数量的活动停车场。

相关问题