mysql - SQL语句中LIKE里面的CAST？

Question

我有这张表:

CREATE TABLE AccessUser
(
    `AccessUserID` int, 
    `AccessUserName` varchar(255), 
    `AccessUserType` int, 
    `AccessUserGroups` varchar(255)
);

INSERT INTO AccessUser
(   `AccessUserID`, 
    `AccessUserName`, 
    `AccessUserType`, 
    `AccessUserGroups`
)
VALUES
    (1, 'Group 1', 2, ''),
    (2, 'Group 2', 2, ''),
    (3, 'John', 5, '@1@'),
    (4, 'Nick', 5, '@1@@2@');

问题:我如何获得此结果(基于 AccessUserGroups，它指的是 AccessUserID)？

| NAME | FIRMNAME |
|------|----------|
| John |  Group 1 |
| Nick |  Group 1 |
| Nick |  Group 2 |

我试过类似的方法，但出现错误:

SELECT DISTINCT
Empl.AccessUserName AS Name,
Firm.AccessUserName AS FirmName
FROM AccessUser AS Firm, AccessUser AS Empl
WHERE Empl.AccessUserType = 5
AND Empl.AccessUserGroups LIKE ('%@' + CAST(Firm.AccessUserID AS VARCHAR(10)) + '@%')
ORDER BY Empl.AccessUserName ASC

错误:

“您的 SQL 语法有误；请查看与您的 MySQL 服务器版本相对应的手册，了解要使用的正确语法” 'VARCHAR(10)) + '@%') 按 Empl.AccessUserName ASC 排序' 在第 6 行:...[再次使用相同的 sql]

Answer 1

如果您使用 CONCAT转换是隐式的:

SELECT DISTINCT
Empl.AccessUserName AS Name,
Firm.AccessUserName AS FirmName
FROM AccessUser AS Firm, AccessUser AS Empl
WHERE Empl.AccessUserType = 5
AND Empl.AccessUserGroups LIKE CONCAT('%@', Firm.AccessUserID, '@%')
ORDER BY Empl.AccessUserName ASC;

Example on SQL Fiddle