高分求一个mysql自定义函数

Question

mysql> select * from RLTN_WORKS_CATALOG limit 0, 10;
+------------+---------+--------+--------+--------+-------------+
| idArtwork  | Catalog | Volume | Number | Suffix | NotVerified |
+------------+---------+--------+--------+--------+-------------+
| OPP.00:001 | DB      | II     |     10 |        |           0 |
| OPP.00:001 | P       | I      |    509 |        |           0 |
| OPP.00:001 | Z       | I      |     41 |        |           0 |
| OPP.00:002 | DB      | II     |     12 |        |           0 |
| OPP.00:002 | MPB     |        |      4 | .263   |           0 |
| OPP.00:002 | P       | I      |    498 |        |           0 |
| OPP.00:002 | Z       | I      |     24 |        |           0 |
| OPP.00:004 | P       | I      |    401 |        |           0 |
| OPP.00:004 | PP      | 9900   |     64 |        |           0 |
| OPP.00:004 | Z       | I      |     18 |        |           0 |
+------------+---------+--------+--------+--------+-------------+现在我想用一个自定义函数把Catalog, volume,Number,Suffix连接起来，连接规则是：Catalog.Volume:NumberSuffix
如上面的第一个行：DB:II.10
第五行：MPB:4.263自定义函数名为：FormatCatalog，用下面的mysql语句：
SELECT FormatCatalogEntry(R.Catalog,R.Volume,R.Number,R.Suffix) FROM RLTN_WORKS_CATALOG R limit 0, 10;
应该得到这样的结果：
+------------------+
| DB.II:10         |
| P.I:509          |
| Z.I:41           |
| DB.II:12         |
| MPB:4.263        |
| P.I:498          |
| Z.I:24           |
| P.I:401          |
| PP.9900:64       |
| Z.I:18           |
+------------------+谢谢高手了！

Answer 1

直接CONCAT不行？
select *,concat(Catalog, volume,Number,Suffix) from RLTN_WORKS_CATALOG limit 0, 10; 

Answer 2

select concat(Catalog,'.', volume,':',Number,Suffix)
FROM RLTN_WORKS_CATALOG limit 0, 10; 
直接concat不行吗？

Answer 3

直接CONCAT不行？
select *,concat(Catalog,'.',volume,Number,':',Suffix) from RLTN_WORKS_CATALOG limit 0, 10; 

Answer 4

CONCAT可以的另外字符串连接,还可以用:mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)mysql> select 'a'||'b';
+----------+
| 'a'||'b' |
+----------+
| ab       |
+----------+
1 row in set (0.00 sec)mysql>

Answer 5

楼上厉害，我还以为||没在mySQL中实现呢一直。 

Answer 6

不是吧，||在MYSQL是 OR 的意思吧
OR, || Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL. mysql> SELECT 1 || 1;
        -> 1
mysql> SELECT 1 || 0;
        -> 1
mysql> SELECT 0 || 0;
        -> 0
mysql> SELECT 0 || NULL;
        -> NULL
mysql> SELECT 1 || NULL;
        -> 1

Answer 7

各位高手，不能用concat， 因为有的记录没有Volume一项的。就变成了Catalog:Number,
如果有volume,就是Catalog.Volume:Number我想可能还是要自己定义函数

Answer 8

还是可以用内建函数实现的。
select concat(Catalog,if(isnull(volume),'',concat('.', volume)),':',Number,Suffix)
FROM RLTN_WORKS_CATALOG limit 0, 10; 

Answer 9

对NULL判断一下就可以了select concat(Catalog,COALESCE(volume,''),'.',':',Number,Suffix)
FROM RLTN_WORKS_CATALOG limit 0, 10; 

Answer 10

还有一句:mysql> set sql_mode='ansi'; 
Query OK, 0 rows affected (0.00 sec) 曾见人用存储过程写的一个连接不同字符串方法中,用到

Answer 11

这是根据你的结果：
 DB.II:1                                                                     |
| DB.DII:1                                                                    |
| DB.V:1                                                                      |
| LD.:1                                                                       |
| DB.VI:1                                                                     |
| MPM.:1.16.A                                                                 |
| DB.III:1                                                                    |
| DB.DIII:1                                                                   |
| DB.IV:1                                                                     |
| DB.DIV:1   可我要得结果是：
+------------------+
| DB.II:10        |
| P.I:509          |
| Z.I:41          |
| DB.II:12        |
| MPB:4.263        |
| P.I:498          |
| Z.I:24          |
| P.I:401          |
| PP.9900:64      |
| Z.I:18          |
+------------------+ 

Answer 12

这个也不对：
mysql> select concat(Catalog,COALESCE(volume,''),'.',':',Number,Suffix)
    -> FROM RLTN_WORKS_CATALOG limit 0, 10;
+-----------------------------------------------------------+
| concat(Catalog,COALESCE(volume,''),'.',':',Number,Suffix) |
+-----------------------------------------------------------+
| DBII.:1                                                   |
| DBDII.:1                                                  |
| DBV.:1                                                    |
| LD.:1                                                     |
| DBVI.:1                                                   |
| MPM.:1.16.A                                               |
| DBIII.:1                                                  |
| DBDIII.:1                                                 |
| DBIV.:1                                                   |
| DBDIV.:1                                                  |
+-----------------------------------------------------------+
10 rows in set (0.00 sec)

Answer 13

我自己解决了：CREATE FUNCTION FormatCatalog(catalog varchar(5), volume varchar(15),number INT, suffix varchar(15)) 
  RETURN S VARCHAR(50)
  
  BEGIN
    DECLARE s VARCHAR(50);    SET s = CONCAT(catalog,IF(STRCMP(volume,'')=0,'',CONCAT('.',volume)));    IF STRCMP(catalog,'PP')=0 THEN
      IF number<10 THEN
        SET s = CONCAT(s,':','00',CAST(number AS CHAR));
      ELSEIF number<100 THEN
        SET s = CONCAT(s,':','0',CAST(number AS CHAR));
      ELSE SET s = CONCAT(s,':',CAST(number AS CHAR));
      END IF;
    ELSE
      SET s = CONCAT(s,':',CAST(number AS CHAR));
    END IF;    SET s = CONCAT(s,IF(STRCMP(suffix,'')=0,'',suffix));
      
    RETURN s;
  END //还是谢谢高手给我提供的思路，我把分还是给你们