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

我有一个像这样的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_id作为主键的客户,第二个是具有外键customer_id的plus_membership,plus_membership表是一个表,如果客户请求成为加号成员则显示请求,状态1表示客户被认为是加号成员.我需要选择customer表并添加别名列让我们说别名列名是成员资格,只显示常规或加号,加上意味着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 |

+-----------+----+---------+----------+

解决方法:

您可以在两个表之间使用左连接,并使用Case .. When条件表达式来相应地评估成员资格.

Left Join将确保考虑来自customer表的所有客户,无论他们是否在plus_membership表中具有相应的匹配行.

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

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

标签：mysql,join,select,alias

来源： https://codeday.me/bug/20190701/1346091.html