如何在SQL查询中基于另一列的值创建/添加一列？

Question

我想通过一个SQL选择查询，在满足条件的情况下动态添加另一列hook_name。

比如说，如果hook_type = 0，则表格hook_name应该有一个值为OFFER，同样地，对于hook_type = 1

，hook_name应该显示为“ACCEPT”。

以下是结果的截图：

查询语句如下:

select hook_type, 'hook name' as hook_name,
       count(*) as number_of_exchange_activities 
from `exchange` 
group by hook_type # hook_type 0 for OFFER, 1 for ACCEPT and 2 for offer EXPIRED;

提前致谢。

- Ahsan Zaheer

Answer 1

使用标准的 SQL CASE：

SELECT hook_type, 
   CASE hook_type
      WHEN 0 THEN 'OFFER'
      WHEN 1 THEN 'ACCEPT'
      WHEN 2 THEN 'EXPIRED'
   END AS hook_name,
   COUNT(*) AS number_of_exchange_activities 
FROM `exchange` 
GROUP BY hook_type

- dnoeth

Answer 2

select case when hook_type = 0 then 'offer'
            when hook_type = 1 then 'accept'
            else 'expired'
       end as hook_t, 
      `hook name` as hook_name,
      count(*) as number_of_exchange_activities 
from `exchange` 
group by hook_t

顺便提一下，我认为您想转义列名 hook name。因此，请使用反引号而不是引号。

- juergen d