mysql连接和计数

Question

以某种方式未能成功创建我想要的查询。

DB是跟locations有关的，相关的有下表

• t_location - 位置列表，包括。字段 t_location_zipcode 和 t_location_id_location
• t_zipcodecity - 仅连接表 t_zipcodecity_zipcode t_zipcodecity_id_city
• t_city - 包含 t_city_id_city
的城市列表
• t_citystate - 连接表，t_citystate_id_city，t_citystate_id_state
• t_state - 具有 t_state_id_state 的状态列表

最初我尝试使用此查询获取包含位置的州列表:

SELECT DISTINCT `t_state_id_state`
      , `t_state_name_full` 
  FROM (`t_state`) 
LEFT JOIN `t_citystate` ON `t_state_id_state` = `t_citystate_id_state` 
LEFT JOIN `t_city` ON `t_citystate_id_state` = `t_city_id_city` 
LEFT JOIN `t_zipcodecity` ON `t_city_id_city` = `t_zipcodecity_id_city` 
LEFT JOIN `t_location` ON `t_zipcodecity_zipcode` = `t_location_zipcode` 
ORDER BY `t_state_name_full` asc ­

效果很好。

现在我还需要/想要的是获得每个州的位置数量，但我却惨遭失败。我不知道是否可以在这个查询中完成，或者我是否需要另一个查询，无论哪种方式我都需要帮助!

Answer 1

您可以使用计数和分组依据。像这样:

SELECT DISTINCT `t_state_id_state`
          , `t_state_name_full`
          , COUNT(*) 
      FROM (`t_state`) 
 LEFT JOIN `t_citystate` ON `t_state_id_state` = `t_citystate_id_state` 
 LEFT JOIN `t_city` ON `t_citystate_id_state` = `t_city_id_city` 
 LEFT JOIN `t_zipcodecity` ON `t_city_id_city` = `t_zipcodecity_id_city` 
 LEFT JOIN `t_location` ON `t_zipcodecity_zipcode` = `t_location_zipcode` 
  GROUP BY `t_state_id_state` , `t_state_name_full`
  ORDER BY `t_state_name_full` asc