高分求一个mysql自定义函数
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 |
+------------------+
谢谢高手了!