mysql-group不使用多个case-when语句

我正在使用kickstarter数据库开发一个数据库，我正在尝试：
定义短期、中期和长期活动
看一看每一段竞选活动能提高多少
将不同货币兑换成美元
我的原始代码如下：

SELECT 
    CASE 
        WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
        WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
        ELSE 'Long Campaign'
        END AS 'Campaign Length',
    CASE 
        WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
        WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
        WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
        WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
        WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
        WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
        WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
        WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
        WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
        WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
        WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
        WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
        WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
        ELSE ROUND(SUM(campaign.pledged),2)
    END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`;

我期待的结果是：

Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX

其中是转换后质押金额的总和。原始代码显示：

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ks_data.currency.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

所以我即兴添加了额外的分组依据，在意识到我不能分组依据之后 Amount Raised :

SELECT 
    CASE 
        WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
        WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
        ELSE 'Long Campaign'
        END AS 'Campaign Length',
    CASE 
        WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
        WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
        WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
        WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
        WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
        WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
        WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
        WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
        WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
        WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
        WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
        WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
        WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
        ELSE ROUND(SUM(campaign.pledged),2)
    END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`,currency.id;

但结果并不像我想的那样：

Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Short Campaign.       ---- XXXXXXXXXXXXX

我试过很多方法，但都找不到解决办法

你得把车开走 case 聚合函数中货币名称的表达式：

SELECT 
    CASE 
        WHEN DATEDIFF(ca.deadline,ca.launched) <= 30 THEN 
            'Short Campaign' 
        WHEN DATEDIFF(ca.deadline,ca.launched) <= 60 
            THEN 'Mid-lengthed Campaign'
        ELSE 'Long Campaign'
    END AS campaign_length,
    ROUND(SUM(
        ca.pledged 
        * CASE cu.name
            WHEN 'GBP' THEN 0.8
            WHEN 'CAD' THEN 1.36
            WHEN 'AUD' THEN 1.43
            ...
            ELSE 1
        END
    ), 2) as amount_raised
FROM campaign ca
LEFT JOIN currency cu ON cu.id = ca.currency_id 
GROUP BY campaign_length;

其他注意事项：
不要对列标识符使用单引号-它们是用来表示文字字符串的（mysql需要反引号）；事实上，使用不需要引用的标识符更简单
两者 case 表达式可以简化，如上所示；在第一个分支中，不需要检查第二个分支中的活动持续时间是否大于30（第一个分支已捕获）；在第二种方法中，可以使用short-circuit形式，而不是在每个分支中重复列名
表别名使查询更易于编写和读取

mysql-group不使用多个case-when语句

1条答案

相关问题

热门标签

最新问答