MySQL 左连接 COUNT() 和 SUM() 与来自同一个表的 CASE

Question

我有一系列一对多关系的下表:

company_company、company_portfolio、building_site 和 stats_meter。我遇到的困难是决赛 table ，statistics_meter。

为了便于本练习，其结构如下:

同一个表中的记录是相关的，有些是父仪表，有些是子仪表。如果一条记录是子记录，它将设置parent_meter_id 和building_id，这一点至关重要，这就是将表LEFT JOIN 设置为NULL 的方式。

 id  | parent_meter_id | site_ref            | building_id
 1   | NULL            | some building       | 45
 2   | NULL            | some other building | 45
 3   | 1               | and another         | NULL
 4   | 1               | one another one     | NULL
 5   | 2               | final one           | NULL

我有两个要求:

1 - 计算设置了building_id的父表的数量(我正在成功完成)

2 - 计算parent_meter_id与(1)中计数的meter_id匹配的米数

因此，我期望得到 (1) = 2 且 (2) = 3 的结果。

这是我到目前为止得到的 SQL...我尝试摆弄 SUM 案例，但我认为这是完全错误的。这在一个查询中是否可能实现？

感谢您的帮助。

        SELECT
        building_site.id as site_id,
        building_site.site_ref as building_name,
        COUNT(statistics_meter.id) AS meter_count,
        SUM(CASE WHEN statistics_meter.parent_meter_id = [???] THEN 1 ELSE 0 END) AS check_meter_count
        FROM company_company
        LEFT JOIN company_portfolio ON company_portfolio.company_id=company_company.id
        LEFT JOIN building_site ON building_site.portfolio_id=company_portfolio.id
        LEFT JOIN statistics_meter ON statistics_meter.building_id=building_site.id
        WHERE company_company.id=41
        GROUP BY building_site.id

Answer 1

好吧，如果我理解你的意思，你需要使用子查询来获取带有建筑物 ID 的父仪表，然后将其连接到主表。 SQL Fiddle

select
sm.id,
sm.parent_meter_id,
sm2.id as ID2,
sm.site_ref,
sm.building_id
from
statistics_meter sm
inner join (
  select
  id,
  parent_meter_id
  from
  statistics_meter
  where
  building_id is not null) sm2
  on sm.parent_meter_id = sm2.id