--sql server 语句:CREATE FUNCTION [dbo].[m_BB]
(
@a VARCHAR(2000) ,
@b VARCHAR(2000) ,
@split VARCHAR(2)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @t1 TABLE ( col VARCHAR(2000) )
WHILE ( CHARINDEX(@split, @a) <> 0 )
BEGIN
IF ( SUBSTRING(@a, 1, CHARINDEX(@split, @a) - 1) != ' ' )
BEGIN
INSERT @t1
( col
)
VALUES ( SUBSTRING(@a, 1, CHARINDEX(@split, @a) - 1)
)
END
SET @a = STUFF(@a, 1, CHARINDEX(@split, @a), '')
END
INSERT @t1
( col )
VALUES ( @a )
DECLARE @t2 TABLE ( col VARCHAR(2000) )
WHILE ( CHARINDEX(@split, @b) <> 0 )
BEGIN
IF ( SUBSTRING(@b, 1, CHARINDEX(@split, @b) - 1) != ' ' )
BEGIN
INSERT @t2
( col
)
VALUES ( SUBSTRING(@b, 1, CHARINDEX(@split, @b) - 1)
)
END
SET @b = STUFF(@b, 1, CHARINDEX(@split, @b), '')
END
INSERT @t2
( col )
VALUES ( @b )
DECLARE @sql VARCHAR(2000)
SET @sql = ''
SELECT @sql = @sql + ISNULL((case when b.col<>'' then b.col + '(<font color=Blue>已签</font>)' else b.col end), a.col+'(<font color=red>未签</font>)') + ','
FROM @t1 a
LEFT JOIN @t2 b ON a.col = b.col
RETURN @sql
END转换成mysql语句。高手们指点
搞半天了 老是提示有错 但是不知道错误在什么地方
mysql的语句是:CREATE FUNCTION `hr_dbo`.`m_BB`(
a VARCHAR(2000) ,
b VARCHAR(2000) ,
split VARCHAR(2)
)
RETURNS VARCHAR(2000)
BEGIN
DECLARE t1 TABLE ( col VARCHAR(2000));
WHILE ( LOCATE(split, a) <> 0 ) DO
IF ( SUBSTRING(a, 1, LOCATE(split, a) - 1) <> ' ' ) THEN
INSERT t1
( col
)
VALUES ( SUBSTRING(a, 1, LOCATE(split, a) - 1)
)
END IF;
SET a = INSERT(a, 1, LOCATE(split, a), '');
END WHILE;
INSERT t1
( col )
VALUES ( a )
DECLARE t2 TABLE ( col VARCHAR(2000) );
WHILE ( LOCATE(split, b) <> 0 ) DO
IF ( SUBSTRING(b, 1, LOCATE(split, b) - 1) <> ' ' ) THEN
INSERT t2
( col
)
VALUES ( SUBSTRING(b, 1, LOCATE(split, b) - 1)
)
END IF;
SET b = INSERT(b, 1, LOCATE(split, b), '') ;
END WHILE;
INSERT t2
( col )
VALUES ( b )
DECLARE sqlstr VARCHAR(2000);
SET sqlstr = '';
SELECT sqlstr = CONCAT(sqlstr,IFNULL((CASE WHEN b.col<>'' THEN CONCAT(b.col,'(<font color=Blue>已签</font>)') ELSE b.col END), CONCAT(a.col,'(<font color=red>未签</font>)')),',')
FROM t1 a
LEFT JOIN t2 b ON a.col = b.col
RETURN sqlstr ;
END$$
提示错误:
查询:CREATE FUNCTION `hr_dbo`.`m_BB`( a VARCHAR(2000) , b VARCHAR(2000) , split VARCHAR(2) ) RETURNS VARCHAR(2000) BEGIN DECLARE t1 T...错误代码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE ( col VARCHAR(2000));
WHILE ( LOCATE(split, a) <> 0 ) do
' at line 8执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------
实在找不出错误到底错在什么地方了!唉!
比如:A字段中有:A,B,C,D
B字段中有:A,B,C,D,E,F
两个字段比较之后得到的结果是:
A(已有),B(已有),C(已有),D(已有),E(无),F(无)隔开的标点是“,”号。
http://bbs.csdn.net/topics/390263280
中我的回答