mysql-将字段连接到附加字段

rqcrx0a6  于 2021-06-24  发布在  Mysql
关注(0)|答案(2)|浏览(339)

简单的一个真的-或者应该是。
我有多个字段要压缩并附加到一个长文本字段。
我猜这是一个concat函数,但我不知道如何将串联的值附加到具有现有值的同一行中的字段。
是有一个mysql语句可以实现这个技巧,还是我必须运行一个循环php脚本?
例子。。。

ID | NAME | POSITION | NOTES
1 | Richard | Programmer | Not paid enough
2 | David | Manager | Lazy
3 | Hilary | Personnel Manager | Doesn't care

最后,我想删除name和position字段,但我想预先存储这些值,以便连接“-.name.”和“-.position并将其附加到notes中,而不会丢失该字段中的任何数据,因此结果将是。。。

ID | NAME | POSITION | NOTES
1 | Richard | Programmer | Not paid enough - Richard - Programmer
2 | David | Manager | Lazy - David - Manager
3 | Hilary | Personnel Manager | Doesn't care - Hilary - Personnel Manager

这有道理吗?

oogrdqng

oogrdqng1#

drop table if exists t;
create table t(ID int, NAME varchar(20), POSition varchar(20),NOTES varchar(500));
insert into t values
(1 , 'Richard' , 'Programmer' ,      'Not paid enough'),
(2 , 'David'   , 'Manager'    ,      'Lazy'),
(3 , 'Hilary'  , 'Personnel Manager','Doesnt care');

update t
set notes =
    CONCAT(NOTES, ' - ', NAME, ' - ', POSITION) 
where 1 = 1;

select * from t;

+------+---------+-------------------+------------------------------------------+
| ID   | NAME    | POSition          | NOTES                                    |
+------+---------+-------------------+------------------------------------------+
|    1 | Richard | Programmer        | Not paid enough - Richard - Programmer   |
|    2 | David   | Manager           | Lazy - David - Manager                   |
|    3 | Hilary  | Personnel Manager | Doesnt care - Hilary - Personnel Manager |
+------+---------+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

最后,我想删除姓名和职位字段,你确定要这样做吗?再次取出碎片是件痛苦的事。
如果任何元素为null,concat将返回null如果一个元素不能保证有值,那么ifnull测试是必要的

update t
    set notes =
        CONCAT(IFNULL(NOTES,''), ' - ', IFNULL(NAME,''), ' - ', IFNULL(POSITION,'')) 
    where 1 = 1;

drop table if exists t;
create table t(ID int, NAME varchar(20), POSition varchar(20),NOTES varchar(500));
insert into t values
(1 , 'Richard' , 'Programmer' ,      'Not paid enough'),
(2 , 'David'   , 'Manager'    ,      'Lazy'),
(3 , 'Hilary'  , 'Personnel Manager','Doesnt care'),
(4 , 'HILARY'  , NULL,'DOES NOW') ;

update t
set notes =
    CONCAT(IFNULL(NOTES,''), ' - ', IFNULL(NAME,''), ' - ', IFNULL(POSITION,'')) 
where 1 = 1;

+------+---------+-------------------+------------------------------------------+
| ID   | NAME    | POSition          | NOTES                                    |
+------+---------+-------------------+------------------------------------------+
|    1 | Richard | Programmer        | Not paid enough - Richard - Programmer   |
|    2 | David   | Manager           | Lazy - David - Manager                   |
|    3 | Hilary  | Personnel Manager | Doesnt care - Hilary - Personnel Manager |
|    4 | HILARY  | NULL              | DOES NOW - HILARY -                      |
+------+---------+-------------------+------------------------------------------+
4 rows in set (0.00 sec)
n3h0vuf2

n3h0vuf22#

这个 CONCAT 函数可以在mysql中轻松处理此问题:

SELECT
    ID,
    NAME,
    POSITION,
    CONCAT(NOTES, ' - ', NAME, ' - ', POSITION) AS NOTES
FROM yourTable;

相关问题