MySQL加入 View ，将列连接成一个字符串

Question

我从工作正常的表中创建了一个 View 。问题是在我的表中，我将“FIRSTNAME”和“LASTNAME”作为单独的列。在我看来，我想将它们连接在一起作为一个全名。

MySQL:

CREATE VIEW leader_table AS
SELECT 

 lgl_leaders.LEAD_ID,
 lgl_type.LG_TYPE,
 member_db.FIRSTNAME AS PRIMARY_FNAME,
 member_db.LASTNAME AS PRIMARY_LNAME,
 lgl_leaders.LG_ADDRESS,
 lgl_leaders.LG_CITY,
 lgl_leaders.LG_ZIP,
 lgl_leaders.LG_LAT,
 lgl_leaders.LG_LNG,
 lgl_leaders.CHILDCARE,
 lgl_leaders.LG_DAY,
 lgl_leaders.LG_TIME

 FROM
   lgl_leaders,lgl_type,member_db
 WHERE
       lgl_type.IDNUM = lgl_leaders.LG_TYPE
 AND   member_db.IDNUM = lgl_leaders.PRIMARY_LEADER

一个例子是:

member_db.FIRSTNAME:“BOB”
member_db.LASTNAME: "史密斯"

我想要一个名为“FULLNAME”的单元格读取:“BOB SMITH”而不是单独的列。

我将如何修改我的 View 以将两个字符串连接在一起？

Answer 1

使用 CONCAT()

CREATE VIEW leader_table AS
SELECT 

 lgl_leaders.LEAD_ID,
 lgl_type.LG_TYPE,
 CONCAT(member_db.FIRSTNAME ," ",member_db.LASTNAME ) FULL_NAME,
 lgl_leaders.LG_ADDRESS,
 lgl_leaders.LG_CITY,
 lgl_leaders.LG_ZIP,
 lgl_leaders.LG_LAT,
 lgl_leaders.LG_LNG,
 lgl_leaders.CHILDCARE,
 lgl_leaders.LG_DAY,
 lgl_leaders.LG_TIME

 FROM
   lgl_leaders,lgl_type,member_db
 WHERE
       lgl_type.IDNUM = lgl_leaders.LG_TYPE
 AND   member_db.IDNUM = lgl_leaders.PRIMARY_LEADER