mysql - 在同一个 SELECT 中使用 MySQL Keywork

Question

我正在处理一些 MySQL 查询。我希望可能有一种方法可以在同一语句中使用语句的 AS 名称。除了给你举个例子，我不知道还能怎么描述它。

SELECT facebook.Spend,
    validclick.Revenue,
    yahoo.Spend,
    (yahoo.Spend + facebook.spend) AS TSpend,
    (validclick.Revenue - TSpend) AS Profit
FROM validclick
JOIN yahoo ON yahoo.Campaign = validclick.Campaign
JOIN facebook ON facebook.Campaign = validclick.Campaign

我希望能够在其他语句中使用 TSpend 作为名称。我正在使用一些案例陈述和长方程，能够使用这些更简单的名称会使事情变得容易得多。当我尝试 MySQL 时说有一个错误，但你们知道一切，所以如果有办法做到这一点，你们就会知道!

这是实际的查询:

            SELECT validclick.Website AS Website, 
                (yahoo.Spend + facebook.Spend) AS TSpend, 
                (validclick.Revenue - TSpend) AS Profit, 
                (TSpend / Profit) AS ROI, 
            FROM 
                (SELECT camp.CampaignName AS CampaignName, 
                    MAX(camp.Website) AS Website, 
                    SUM(vc.Revenue) AS Revenue 
                FROM 
                    (SELECT AffID, 
                        MAX(CampaignName) AS CampaignName, 
                        Website
                    FROM campaigns 
                    $campaignmanagersql .
                    GROUP BY AffID) AS camp 
                JOIN 
                    (SELECT AffID, 
                        SUM(Clicks) AS Clicks, 
                        SUM(AffiliateRevenue) AS Revenue, 
                    FROM validclickvc 
                    WHERE Date BETWEEN '2018-01-07' AND '2018-01-08' 
                    GROUP BY AffID) AS vc 
                ON vc.AffID = camp.AffID 
                GROUP BY camp.CampaignName) AS validclick 
            LEFT JOIN 
                (SELECT CampaignName, 
                    SUM(Spend) AS Spend,
                    SUM(OutboundClicks) AS PaidClicks 
                FROM facebookads 
                WHERE Date BETWEEN '2018-01-07' AND '2018-01-08' 
                GROUP BY CampaignName) AS facebook 
            ON validclick.CampaignName = facebook.CampaignName 
            LEFT JOIN 
                (SELECT CampaignName, 
                    SUM(Spend) AS Spend,
                    SUM(Clicks) AS PaidClicks 
                FROM yahoogemini 
                WHERE Date BETWEEN '2018-01-07' AND '2018-01-08' 
                GROUP BY CampaignName) AS yahoo 
            ON validclick.CampaignName = yahoo.CampaignName 
            ORDER BY CampaignName

Answer 1

在 MySQL 中，您只能在 GROUP BY 和 HAVING 子句中使用 AS Column。

您可以将查询包装到另一个 SELECT 中:

SELECT * FROM 
    (
    SELECT facebook.Spend,
        validclick.Revenue,
        yahoo.Spend,
        (yahoo.Spend + facebook.spend) AS TSpend,
        (validclick.Revenue - TSpend) AS Profit
    FROM validclick
    JOIN yahoo ON yahoo.Campaign = validclick.Campaign
    JOIN facebook ON facebook.Campaign = validclick.Campaign
    ) AS t1
WHERE TSpend > 10

但是这会对性能产生影响，因为这些计算列的搜索不会涉及索引