mysql - 创建一个 View 以显示一组的平均值

Question

我有一张 table ，

tbl_courses 
------------
int id //pk
int id_formation //fk
int credits
enum lang // en, fr, etc

表格中的数据是这样的

id | id_formation | credits | lang
1 | 101 | 4 | en
2 | 101 | 6 | en
3 | 101 | 5 | fr
4 | 102 | 8 | en
5 | 102 | 2 | fr
6 | 103 | 3 | fr

我想要一个 View 来显示每个 id_formation 的 lang-credits 百分比，例如

id_formation | en_percent | fr_percent
101 | 66 | 33
102 | 80 | 20
103 |  0 | 100

我设法进行了查询，但 mysql 不允许我从中生成 View

SELECT
a.id_formation,
en_percent
//fr_percent
FROM tbl_courses a
JOIN (
  SELECT
  a.id_formation, 
  FORMAT(SUM(a.credits)*100/b.total,0) AS 'en_percent'  
  FROM tbl_courses a
  LEFT JOIN (
    SELECT id_formation, SUM(credits) as total 
    FROM tbl_courses
    GROUP BY id_formation) AS b ON b.id_formation = a.id_formation
  GROUP BY a.id_formation, a.lang HAVING a.lang='en'
) AS en ON en.id_formation=a.id_formation
//repeat join code and switch en to fr
GROUP BY id_formation

我在 JOIN 中重复代码以获取 fr_percent。有没有一种方法可以使它看起来友好？

Answer 1

我用三种语言创建了一个小测试用例。查看最后一个查询。它能给你你需要的吗？如果是这样，我或许可以帮助您创建您想要的 View 。

create table tbl_courses as
   select 1 as id_formation, 'en' as lang, 25 as credits union all
   select 1 as id_formation, 'fr' as lang, 50 as credits union all
   select 1 as id_formation, 'sv' as lang, 25 as credits union all
   select 2 as id_formation, 'en' as lang, 80 as credits union all
   select 2 as id_formation, 'fr' as lang, 15 as credits union all
   select 2 as id_formation, 'sv' as lang,  5 as credits;

alter table tbl_courses add primary key(id_formation, lang);

select id_formation
      ,count(*)     as num_languages
      ,sum(credits) as total_credits
      ,sum(case when lang = 'en' then credits else 0 end)  as en_creds
      ,sum(case when lang = 'fr' then credits else 0  end) as fr_creds
  from tbl_courses
 group 
    by id_formation;

+--------------+---------------+---------------+----------+----------+
| id_formation | num_languages | total_credits | en_creds | fr_creds |
+--------------+---------------+---------------+----------+----------+
|            1 |             3 |           100 |       25 |       50 |
|            2 |             3 |           100 |       80 |       15 |
+--------------+---------------+---------------+----------+----------+
2 rows in set (0.00 sec)