时间戳

首页 > 解决方案 > 如何根据相同的列名对行值进行分组

mysql - 如何根据相同的列名对行值进行分组

问题描述

我已经在 SQL Fiddle 上创建了架构和查询：SQL Fiddle( http://sqlfiddle.com/#!9/e43a13/1 )

我试图根据紧迫性和学术水平来获取工作类型的价格，我成功地得到了它，但我不知道如何以这种方式格式化它：

| Urgency | Academic Level Name 1   |      Academic Level 2    |     Academic Level 3     |
-------------------------------------------------------------------------------------------
| 1 hour  |Price Of Academic Level 1|Price Of Academic Level 2 | Price Of Academic Level 3|
-------------------------------------------------------------------------------------------

我有作为输出：

在此处输入图像描述

标签： mysql

解决方案

如果您正在尝试group by urgency，您可以考虑以下查询。

SELECT 
    utt.UrgencyTime as `Urgency`   
    , sum(case when al.AcademicID = 1 then wtd.Price else 0 end) as `Academic Level Name 1`
    , sum(case when al.AcademicID = 2 then wtd.Price else 0 end) as `Academic Level Name 2`
    , sum(case when al.AcademicID >= 3 then wtd.Price else 0 end) as `Academic Level Name 3`
FROM `WorkTypeData` wtd
JOIN UrgencyTimeTable utt ON utt.UrgencyID = wtd.UrgencyID
JOIN WorkType wt ON wt.WorkTypeID = wtd.WorkTypeID
JOIN AcademicLevel al ON al.AcademicID = wtd.AcademicLevelID
WHERE wt.WorkTypeID = '1' 
GROUP BY utt.UrgencyTime

推荐阅读