mysql - MySQL-将值乘以列，具体取决于该列

我该怎么写呢？大小引用枚举。当列“大小”为：时，我需要通过替换列“大小”来将平均公司规模显示为别名AS“ AverageEstimatedCompanySize”。

1 = 15
2 = 30
3 = 50
4 = 100
5 = 250

换句话说，我的表格将公司规模显示为1、2、3、4或5。而实际上1的公司规模为15。

这都是更大的查询的一部分：

SELECT COUNT(DISTINCT(ID)) AS 'Total # of Opps', AVG(Size*?) AS 'AverageEstimatedCompanySize'
FROM persontable AS POJT INNER JOIN opportunity
ON POJT.ID = opportunity.id
WHERE opportunity.TimeStamp >= '2012-01-01' AND opportunity.TimeStamp <= '2012-12-31' AND POJT.JobTitleID IN
    (SELECT Id
    FROM job
    WHERE CategoryID IN
        (SELECT id
        FROM job_category
        WHERE name IN ('Sc', 'Ma', 'Co', 'En', 'Tr')))

最佳答案

听起来像是用case statement可解决的东西。以下内容未经测试，但应指出正确的方向。

SELECT
    COUNT(DISTINCT(ID)) AS 'Total # of Opps',
    AVG(
        CASE Size
            WHEN 1 THEN 15
            WHEN 2 THEN 30
            WHEN 3 THEN 50
            WHEN 4 THEN 100
            WHEN 5 THEN 250
        END
    ) AS 'AverageEstimatedCompanySize'
FROM persontable AS POJT INNER JOIN opportunity
ON POJT.ID = opportunity.id
WHERE opportunity.TimeStamp >= '2012-01-01' AND opportunity.TimeStamp <= '2012-12-31' AND POJT.JobTitleID IN
    (SELECT Id
    FROM job
    WHERE CategoryID IN
        (SELECT id
        FROM job_category
        WHERE name IN ('Sc', 'Ma', 'Co', 'En', 'Tr')))

关于mysql - MySQL-将值乘以列，具体取决于该列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18902375/