我有一个mysql表,我想在上面运行单个/嵌套查询。

SELECT
`subscriber_id`,
(SELECT...?) AS total_campaigns,
(SELECT...?) AS total_opens,
(total_opens / total_campaigns) as percentage
FROM
`campaigns_table`


值= 2的type列显示该广告系列已打开。

我希望返回具有所有subscriber_id的结果集,该结果集具有总的广告系列和总的打开次数



查询结果数据

+---------------+-----------------+-------------+------------+
| subscriber_id | total_campaigns | total_opens | percentage |
+---------------+-----------------+-------------+------------+
|             1 |               2 |           1 |        0.5 |
|             2 |               2 |           2 |        1.0 |
|             3 |               2 |           0 |        0.0 |
|             4 |               1 |           0 |        0.0 |
|             5 |               1 |           1 |        1.0 |
+---------------+-----------------+-------------+------------+


Subscriber_id 1将是total_campaigns = 2(campaign_id的37428,37239)和total_opens = 1(仅campaign_id 27428具有类型2记录)



表格数据示例

+---------------+-------------+------------+-------+------+---------+| subscriber_id | campaign_id | timestamp | count | type | link_id |+---------------+-------------+------------+-------+------+---------+| 1 | 37428 | 1434513738 | 1 | 1 | 0 || 1 | 37428 | 1434513758 | 1 | 1 | 0 || 1 | 37428 | 1434513338 | 2 | 2 | 0 || 1 | 37429 | 1434511738 | 1 | 1 | 0 || 1 | 37429 | 1434311738 | 1 | 1 | 1 || 2 | 37428 | 1534513738 | 1 | 1 | 0 || 2 | 37428 | 1534513758 | 1 | 1 | 0 || 2 | 37428 | 1534513338 | 2 | 2 | 0 || 2 | 37429 | 1534511738 | 1 | 1 | 1 || 2 | 37429 | 1534311738 | 1 | 2 | 0 || 3 | 37428 | 1534513738 | 1 | 1 | 0 || 3 | 37429 | 1534511738 | 1 | 1 | 1 || 4 | 57428 | 1534513738 | 1 | 1 | 0 || 4 | 57428 | 1534513758 | 1 | 1 | 0 || 5 | 57428 | 1534513338 | 3 | 2 | 0 |+---------------+-------------+------------+-------+------+---------+



使用下面@ spencer7593的答案。然后如何使用结果更新另一个表?

试图做这样的事情(我怎么有它不起作用)

    UPDATE `subscribers` a
    LEFT JOIN `campaigns_table` b ON a.`ID` = b.`subscriber_id`
    SET a.`STATUS` = 2
    FROM(
        SELECT t.subscriber_id
             , COUNT(DISTINCT t.campaign_id)                   AS total_campaigns
             , COUNT(DISTINCT IF(t.type=2,t.campaign_id,NULL)) AS open_campaigns
             , COUNT(DISTINCT IF(t.type=2,t.campaign_id,NULL))
             / COUNT(DISTINCT t.campaign_id)                   AS percentage
        FROM `campaigns_table` t
        GROUP BY t.subscriber_id
        HAVING COUNT(DISTINCT t.campaign_id) > 5 AND COUNT(DISTINCT IF(t.type=2,t.campaign_id,NULL)) = 0
        ORDER BY t.subscriber_id
    ) i

最佳答案

在我看来,这可以通过条件汇总解决,只需通过表一次即可解决,而无需任何嵌套查询。

SELECT t.subscriber_id
     , COUNT(DISTINCT t.campaign_id)                   AS total_campaigns
     , COUNT(DISTINCT IF(t.type=2,t.campaign_id,NULL)) AS open_campaigns
     , COUNT(DISTINCT IF(t.type=2,t.campaign_id,NULL))
     / COUNT(DISTINCT t.campaign_id)                   AS percentage
  FROM `campaigns_table` t
 GROUP BY t.subscriber_id
 ORDER BY t.subscriber_id




可以通过嵌套查询生成等效的结果,但是通常,单次通过表(使用单个SELECT)将具有更有效的访问计划。

通常,如果有使用嵌套查询的要求(我不知道为什么会这样),通常我希望在SELECT列表中使用内联视图而不是相关查询。

SELECT q.subscriber_id
     , SUM(1)                       AS total_campaigns
     , SUM(q.open_campaign)         AS open_campaigns
     , SUM(q.open_campaign)/SUM(1)  AS percentage
  FROM ( SELECT t.subscriber_id
              , t.campaign
              , MAX(t.type=2) AS `open_campaign`
           FROM `campaigns_table` t
          WHERE t.campaign IS NOT NULL
          GROUP
             BY t.subscriber_id
              , t.campaign
       ) q
 ORDER BY q.subscriber


如果我们要在SELECT列表中使用嵌套查询,则这些查询将是相关子查询...

SELECT s.subscriber
     , (      SELECT COUNT(DISTINCT t.campaign)
                FROM `campaigns_table` t
               WHERE t.subscriber = s.subscriber
       ) AS total_campaigns

     , (      SELECT COUNT(DISTINCT o.campaign)
                FROM `campaigns_table` o
               WHERE o.subscriber = s.subscriber
                 AND o.type=2
       ) AS open_campaigns

     , (      SELECT COUNT(DISTINCT po.campaign)
                FROM `campaigns_table` po
               WHERE po.subscriber = s.subscriber
                 AND po.type=2
       )
     / (      SELECT COUNT(DISTINCT pt.campaign)
                FROM `campaigns_table` pt
                WHERE pt.subscriber = s.subscriber
       ) AS percentage

  FROM ( SELECT r.subscriber
           FROM `campaigns_table` r
         GROUP BY r.subscriber
       ) s
 ORDER BY s.subscriber

关于mysql - 多个嵌套的SELECT查询并求和两列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46614484/

10-10 10:55
查看更多