php - SQL在MySQL中使用JOIN选择通用数据和剩余数据

我有三个表：wi_district，wi_group和wi_training。我需要根据地区来计算小组和培训。为此，我使用了以下SQL；


SQL1


选择wi_district.dst_name，COUNT（grp_id）AS group_count，MAX（grp_created_date）作为grp_created_date
来自wi_group
内联接wi_district ON wi_district.dst_id = wi_group.grp_dst_id AND wi_group.grp_deleted = 0 AND wi_group.grp_type IN（3）
GROUP BY wi_district.dst_name

每个区的查询计数组。同样


SQL2


选择wi_district.dst_name，COUNT（trn_id）作为training_count，MAX（trn_created_date）作为trn_created_date
来自wi_training
内联接wi_district ON wi_district.dst_id = wi_training.dst_id AND wi_training.trn_deleted = 0 AND wi_training.trn_beneficiary_type IN（-1,2,8,9,10）
GROUP BY wi_district.dst_name

该查询统计每个地区的培训情况。现在，我需要组合从SQL1和SQL2获得的所有结果，并以的形式获得结果

dst_name || group_count || grp_created_date || training_count || trn_created_date

问题是，每当我使用SQL1 LEFT JOIN SQL2时，它就分别显示与SQL1对应的结果，而无法获得SQL2的结果，反之亦然。请帮助我解决MySQL中的这个问题

最佳答案

我认为您可以加入过滤后的表格，然后按地区名称分组。像这样：

SELECT dist.dst_name AS dst_name,
COUNT(grp.grp_id) AS group_count, MAX(grp.grp_created_date) AS grp_created_date,
COUNT(trn.trn_id) AS training_count, MAX(trn.trn_created_date) AS trn_created_date
FROM wi_district AS dist
LEFT JOIN (
  SELECT dst_id, trn_id, trn_created_date
  FROM wi_training
  WHERE trn_deleted=0
    AND trn_beneficiary_type IN (-1,2,8,9,10)
) AS trn ON trn.dst_id=dist.dst_id
LEFT JOIN (
  SELECT grp_dst_id, grp_id, grp_created_date
  FROM wi_group
  WHERE grp_deleted=0
    AND grp_type IN (3)
) AS grp ON grp.grp_dst_id = dist.dst_id
GROUP BY dist.dst_name