mysql - 根据现有列中的值创建 2 列

Question

我有下表。我想添加 2 个带有选择查询的新列，该查询将根据标志类型显示总计。

表:

tt        | company     | count  | flag
  --------------------------------------------
  123     | adeco       | 5      | 1
  123     | mic         | 4      | 2 
  333     | manpower    | 88     | 2
  444     | linar       | 2      | 2
  555     | dlank       | 3      | 1

期望:

tt        | company     | total  | flag  | total_flag1 | total_flag2
  -------------------------------------------------------------------
  123     | adeco       | 5      | 1     | 5           | 0
  123     | mic         | 4      | 2     | 0           | 4 
  333     | manpower    | 88     | 2     | 0           | 88
  444     | linar       | 2      | 2     | 0           | 2
  555     | dlank       | 3      | 1     | 3           | 0

Answer 1

根据您想要的结果，您应该使用 case when 或 if 语法来实现:

select 
    yourtable.*,
    case when flag = 1 then `count` else 0 end as total_flag1,
    case when flag = 2 then `count` else 0 end as total_flag2
from yourtable

或者

select 
    yourtable.*,
    if(flag = 1, `count`, 0) as total_flag1,
    if(flag = 2, `count`, 0) as total_flag2
from yourtable