在mysql中使用自定义字符串创建别名列

nwlqm0z1 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(298)

我有一个mysql表，如下所示：


## customer##

+-----------+----+---------+
|customer_id|name|telephone|
+-----------+----+---------+
|     1     |Andi|+62932011|
|     2     |Boby|+62928291|
|     3     |Jane|+62932212|
|     4     |John|+62999021|
|     5     |Beth|+62999021|
|     6     |Noel|+62999021|
+-----------+----+---------+

## plus_membership##

+-----------------+-----------+-------+------------+
|plus_membership_id|customer_id|status |requested_at|
+------------------+-----------+-------+------------+
|        1         |     1     |   1   | 2018-11-01 |
|        2         |     2     |   0   | 2018-11-03 |
|        3         |     4     |   2   | 2018-11-04 |
|        4         |     6     |   1   | 2018-11-05 |
+------------------+-----------+-------+------------+

在上面的结构中有两个表，第一个是 customer 与 customer_id 作为主键，第二个是 plus_membership 哪个有外键 customer_id ，的 plus_membership 表是一个表，用于显示客户请求成为plus会员时的请求、状态 1 表示客户被批准为plus会员。我需要选择customer表并添加alias列，假设alias列名是membership，只显示 regular 或者 plus , plus 是指 plus_membership 状态为 1 ，如果客户不在 plus_membership 表或状态无效 1 在成员表中。例如：

SELECT *, .... AS membership FROM customer;

+-----------+----+---------+----------+
|customer_id|name|telephone|membership|
+-----------+----+---------+----------+
|     1     |Andi|+62932011|   Plus   |
|     2     |Boby|+62928291|  Regular |
|     3     |Jane|+62932212|  Regular | 
|     4     |John|+62999021|  Regular |
|     5     |Beth|+62999021|  Regular |
|     6     |Noel|+62999021|   Plus   |
+-----------+----+---------+----------+

mysql Join select Alias

来源：https://stackoverflow.com/questions/53151544/make-an-alias-column-with-custom-string-in-mysql

2条答案

按热度按时间

qyzbxkaa1#

你可以用 Left Join 在两张table之间，使用 Case .. When 要计算的条件表达式 membership 相应地。 Left Join 将确保 customer 表中是否有相应的匹配行 plus_membership 不管是不是table。

SELECT
 c.customer_id, 
 c.name, 
 c.telephone, 
 (CASE WHEN pm.status = 1 THEN 'Plus' ELSE 'Regular' END) AS membership
FROM customer AS c
LEFT JOIN plus_membership AS pm 
  ON pm.customer_id = c.customer_id

另一种方法是使用相关子查询和 Exists() . 通常，这比左连接方法效率低。

SELECT 
  c.customer_id, 
  c.name, 
  c.telephone, 
  CASE WHEN EXISTS (SELECT 1 
                    FROM plus_membership AS pm 
                    WHERE pm.customer_id = c.customer_id AND 
                          pm.status = 1
                   )
       THEN 'Plus' 
       ELSE 'Regular' 
  END AS membership 
FROM customer AS c

赞(0）回复(0）举报 2021-06-18

qqrboqgw2#

我们使用 EXISTS 或者 IN 在另一个表中查找数据。

select customer_id, name, telephone,
  case when customer_id in (select customer_id from plus_membership where status = 1)
       then 'Plus' else 'Regular' end as membership
from customer
order by customer_id;

赞(0）回复(0）举报 2021-06-18

我来回答

在mysql中使用自定义字符串创建别名列

2条答案

相关问题

热门标签

最新问答