mysql - 我们可以对同一个表中的多个列使用 FIND_IN_SET() 函数吗
问题描述
注意:我尝试了许多 SF 解决方案,但没有一个对我有用。这有点挑战性,任何帮助将不胜感激。
下面是我的 SQL-Fiddle 链接:http ://sqlfiddle.com/#!9/6daa20/9
我有以下表格:
CREATE TABLE `tbl_pay_chat` (
nId int(11) NOT NULL AUTO_INCREMENT,
npayid int(11) NOT NULL,
nSender int(11) NOT NULL,
nTos varchar(255) binary DEFAULT NULL,
nCcs varchar(255) binary DEFAULT NULL,
sMailBody varchar(500) binary DEFAULT NULL,
PRIMARY KEY (nId)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_bin;
INSERT INTO tbl_pay_chat
(nId,npayid,nSender,nTos,nCcs,sMailBody)
VALUES
(0,1,66,'3,10','98,133,10053','Hi this test maail'),
(0,1,66,'3,10','98,133,10053','test mail received');
_____________________________________________________________
CREATE TABLE `tbl_emp` (
empid int(11) NOT NULL,
fullname varchar(45) NOT NULL,
PRIMARY KEY (empid)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_bin;
INSERT INTO `tbl_emp` (empid,fullname)
VALUES
(3, 'Rio'),
(10, 'Christ'),
(66, 'Jack'),
(98, 'Jude'),
(133, 'Mike'),
(10053, 'James');
我想要的是 :
JOIN 以上两个表以获取 (nTos & nCcs) 列中的全名。
另外,我想要总 COUNT() 行。
我尝试的是以下查询,但在“nTos 和 nCcs 列”中获得倍数时间 FULLNAME 也请建议找到适当的行数。
SELECT a.nId, a.npayid, e1.fullname AS nSender, sMailBody, GROUP_CONCAT(b.fullname ORDER BY b.empid)
AS nTos, GROUP_CONCAT(e.fullname ORDER BY e.empid) AS nCcs
FROM tbl_pay_chat a
INNER JOIN tbl_emp b
ON FIND_IN_SET(b.empid, a.nTos) > 0
INNER JOIN tbl_emp e
ON FIND_IN_SET(e.empid, a.nCcs) > 0
JOIN tbl_emp e1
ON e1.empid = a.nSender
GROUP BY a.nId ORDER BY a.nId DESC;
我希望我的观点很清楚。请帮忙。
解决方案
你有一个可怕的数据模型。您不应该将 id 列表存储在字符串中。为什么?以下是一些原因:
- 数字应存储为数字而不是字符串。
- 表之间的关系应该使用外键关系来声明。
- SQL 的字符串操作能力很差。
- 函数和类型转换
ON
的使用通常会阻止索引的使用。
毫无疑问,还有其他充分的理由。您的数据模型应该为 nm 关系使用正确声明的联结表。
也就是说,有时我们会被其他人的非常、非常、非常、非常糟糕的设计决定所困扰。有一些方法可以解决这个问题。我认为您想要的查询可以表示为:
SELECT pc.nId, pc.npayid, s_e.fullname AS nSender, pc.sMailBody,
GROUP_CONCAT(DISTINCT to_e.fullname ORDER BY to_e.empid)
AS nTos,
GROUP_CONCAT(DISTINCT cc_e.fullname ORDER BY cc_e.empid) AS nCcs
FROM tbl_pay_chat pc INNER JOIN
tbl_emp to_e
ON FIND_IN_SET(to_e.empid, pc.nTos) > 0 INNER JOIN
tbl_emp cc_e
ON FIND_IN_SET(cc_e.empid, pc.nCcs) > 0 JOIN
tbl_emp s_e
ON s_e.empid = pc.nSender
GROUP BY pc.nId
ORDER BY pc.nId DESC;
这是一个 db<>fiddle。
推荐阅读
- python - 列表操作中的 Python 列表
- javascript - 如何更改 vscode 的活动文本编辑器?
- python - 如何将 mark_geoshape 缩放到 Altair 中的特定区域?
- javascript - 使用 javascript 打印动态代码,该代码根据参数添加了一个方法
- python - 如果使用 Python 从负值变为正值,则在列表中查找连续值的索引
- java - 获取每个数组中不存在的元素
- javascript - 基于其他总计 Vanilla JavaScript 计算总计
- java - 将 Mat 对象转换为 Float32[1,66,200,3] android studio
- javascript - 未使用 process.exit() 从节点连接到 sqlite db
- ruby - 我的 bundler 命令无法加载到部署在 Heroku 中的 Sinatra 应用程序中,因此导致它崩溃