主页 / dba / 问题 / 21274
Accepted
shantanuo
Asked: 2012-07-21 20:59:33 +0800 CST

将查询合并为一个

  • 772

以下 2 个查询按预期工作并显示计数。是否可以将它们合并为单个查询？我需要 sum_hits 列作为第二个查询中显示的其余列的一部分。

SELECT date_time, SUM(diac.requests) AS sum_hits
FROM ox_data_summary_ad_hourly diac
JOIN ox_zones z ON (z.zoneid = diac.zone_id)
JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
AND p.agencyid = '3'
AND z.zone_type in ('application')
AND z.delivery in (8)
GROUP BY date_time

SELECT s.date_time AS date_time,
SUM(s.requests) AS sum_requests,
SUM(s.impressions) AS sum_views,
SUM(s.clicks) AS sum_clicks,
SUM(s.conversions) AS sum_conversions,
SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
SUM(s.total_revenue) AS sum_revenue,
SUM(s.total_revenue) AS sum_spend,
SUM(s.total_basket_value) AS sum_bv,
SUM(s.total_num_items) AS sum_num_items,
SUM(s.total_pubrevenue) AS sum_pubrevenue
FROM ox_data_summary_ad_hourly AS s
INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
WHERE a.agencyid = 3
AND z.zone_type = 'application'
AND z.delivery = 8
AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
GROUP BY date_time
mysql subquery

1 个回答

  1. Best Answer
    SELECT B.*,A.sum_hits
FROM
(
    SELECT date_time, SUM(diac.requests) AS sum_hits
    FROM ox_data_summary_ad_hourly diac
    JOIN ox_zones z ON (z.zoneid = diac.zone_id)
    JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
    WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
    AND p.agencyid = '3'
    AND z.zone_type in ('application')
    AND z.delivery in (8)
    GROUP BY date_time
) A INNER JOIN
(    
    SELECT s.date_time AS date_time,
    SUM(s.requests) AS sum_requests,
    SUM(s.impressions) AS sum_views,
    SUM(s.clicks) AS sum_clicks,
    SUM(s.conversions) AS sum_conversions,
    SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
    SUM(s.total_revenue) AS sum_revenue,
    SUM(s.total_revenue) AS sum_spend,
    SUM(s.total_basket_value) AS sum_bv,
    SUM(s.total_num_items) AS sum_num_items,
    SUM(s.total_pubrevenue) AS sum_pubrevenue
    FROM ox_data_summary_ad_hourly AS s
    INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
    INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
    INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
    LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
    LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
    WHERE a.agencyid = 3
    AND z.zone_type = 'application'
    AND z.delivery = 8
    AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
    GROUP BY date_time
) B USING (date_time);
    • 3

相关问题