mysql - 如果未找到连接结果，则选择 COUNT(*) 零

Question

我正在尝试选择给定城市 ID 的所有区域。此外，我正在计算 COUNT(*) 以查看每个区域存在多少个项目。由于项目通过多对多关系与区域相关，因此此 JOIN 必须通过称为 Item_Area 的适当 JOIN 表。

不幸的是，我只得到实际上有最近项目的区域 - 但我想要所有区域。如果最近的项目不存在，则应该简单地为零。我相信这个问题与发布日期的 WHERE 条件有关 - 但我不知道如何使其正常工作。

如何修改以下查询，以选择给定 Area.cityId 的所有区域 - 并简单地计算“recentItems” = 0，对于没有最近项目的区域(即不匹配) Item.published 字段的限制为最近 10 天内。

SELECT
  `Area`.`id`, `Area`.`name`, COUNT(*) AS `recentItems`
  FROM `Area`
  LEFT JOIN `Item_Area` ON `Area`.`id` = `Item_Area`.`areaId`
  LEFT JOIN `Item` ON `Item`.`id` = `Item_Area`.`itemId`
WHERE
  `Area`.`cityId` = "1" AND
  Item.published > DATE_SUB(NOW(), INTERVAL 10 DAY) GROUP BY `Area`.`id`
ORDER BY `Area`.`name` ASC

Answer 1

将有关 Item 的条件从 WHERE 子句移至 ON 子句。并将 COUNT(*) 更改为 COUNT(Item.id):

SELECT
  Area.id, Area.name, COUNT(Item.id) AS recentItems
FROM Area
  LEFT JOIN Item_Area ON Area.id = Item_Area.areaId
  LEFT JOIN Item ON  Item.id = Item_Area.itemId
                 AND Item.published > DATE_SUB(NOW(), INTERVAL 10 DAY) 
WHERE
  Area.cityId = '1' 
GROUP BY 
  Area.id
ORDER BY 
  Area.name ASC ;

请注意，上述内容不会在某些(严格)设置下运行，因为 SELECT 和 ORDER BY 列表中有 Area.name虽然它不在 GROUP BY 列表中。这取决于 sql_mode 是否已设置为 ONLY_FULL_GROUP_BY 。因此，将部分分组更改为:

GROUP BY 
  Area.id, Area.name

<小时/>

您还可以使用别名，以使代码更具可读性(至少对于很多人来说):

SELECT
  a.id, a.name, COUNT(i.id) AS recentItems
FROM 
    Area AS a
  LEFT JOIN 
    Item_Area AS ia ON a.id = ia.areaId
  LEFT JOIN 
    Item AS i ON  i.id = ia.itemId
              AND i.published > DATE_SUB(NOW(), INTERVAL 10 DAY) 
WHERE
  a.cityId = '1' 
GROUP BY 
  a.id
ORDER BY 
  a.name ASC ;

<小时/>

至少有两种更常见的写法。首先在派生表中分组，然后连接:

SELECT
  a.id, a.name, COALESCE(g.recentItems, 0) AS recentItems
FROM 
    Area AS a
  LEFT JOIN 
    ( SELECT 
        ia.areaId, COUNT(*) AS recentItems
      FROM 
          Item_Area AS ia 
        JOIN 
          Item AS i ON  i.id = ia.itemId
      WHERE 
        i.published > DATE_SUB(NOW(), INTERVAL 10 DAY)
      GROUP BY
        ia.areaId
    ) AS g ON a.id = g.areaId
WHERE
  a.cityId = '1' 
ORDER BY 
  a.name ASC ;

或使用内联子查询:

SELECT
  a.id, a.name, 
  COALESCE( 
      ( SELECT 
          COUNT(*)
        FROM 
            Item_Area AS ia 
          JOIN 
            Item AS i ON  i.id = ia.itemId
        WHERE 
          i.published > DATE_SUB(NOW(), INTERVAL 10 DAY)
          AND
          a.id = ia.areaId
      ), 0
          ) AS recentItems
FROM 
    Area AS a
WHERE
  a.cityId = '1' 
ORDER BY 
  a.name ASC ;