我有一个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/