优化复杂的MySQL选择报告

我正在构建一个应用程序, a)每个图书管理员都可以创建一个活动 b)作为该活动的一部分执行的操作在
campaign_actions
中被跟踪,操作是页面加载 为了报告每个广告系列中的操作数量,我为以下数据库结构编写了此SQL查询(针对MySQL),目的是跟踪图书管理员为每个广告系列执行的操作数量: 图书馆员 id |状态 CAMPAIGNS id | librarian_id CAMPAIGN_ACTIONS id | campaign_id |名称 我遇到的问题是: a)我必须在相关的子选择中指定我想要计数的字段 b)因此查询将非常昂贵 我的问题是,由于广告系列有多种操作,如何以更有效的方式有效地计算每个广告系列的操作数量? 较不复杂的查询相当于返回结果集,如下所示:   librarians.id | librarians.status | campaign_actions.name        1 3页X.        1 3页        1 3 pageZ        1 3页A.        1 3页B.        2 3页X   这意味着我必须逐行解析应用程序代码中的结果集,这可能更昂贵。 我很感激你对这个问题的看法。     
已邀请:
将任务分解为较小的任务(视图):
--- campaings per librarian
CREATE VIEW count_librarian_campaigns
AS ( SELECT lib.id AS lib_id
          , COUNT(c.id)
                   AS num_campaigns
     FROM librarians lib
     LEFT JOIN campaigns c
       ON c.librarian_id = lib.id
     GROUP BY lib.id
   )

--- campaign actions per campaign
CREATE VIEW count_campaign_actions
AS ( SELECT c.id   AS c_id
          , COUNT(ca.campaign_id)
                   AS num_actions
     FROM campaigns c
     LEFT JOIN campaign_actions ca
       ON ca.campaign_id = c.id
     GROUP BY c.id
   )
所以,你可以有这样的查询:
SELECT lib.id AS lib_id
     , countlibc.num_campaigns
     , c.id   AS c_id 
     , countca.num_actions
FROM librarians lib
JOIN count_librarian_campaigns countlibc
  ON countlibc.lib_id = lib.id
LEFT JOIN campaigns c
  ON c.librarian_id = lib.id
JOIN count_campaign_actions countca
  ON countca.c_id = c.id
    
这样的事情是否适用于您的目的? SELECT librarians_id,COUNT(librarians_id)FROM(     选择librarians.id作为librarians_id,             librarians.status作为librarians_status,             campaign_actions.name为campaign_actions_name     来自campaign_actions     INNER JOIN活动            ON campaign_actions.campaign_id = campaigns.id     INNER JOIN图书馆员            在campaigns.librarian_id = librarians.id上     GROUP BY campaign_actions.name,librarians.id,librarians.status)as 也许我误解了你的追求。内部查询似乎会返回您在上面表示的表。     

要回复问题请先登录注册