MySQL 在 MATCH AGAINST 中使用 GROUP_CONCAT 字段
我有两张桌子：腰部和面板。我目前有一个成功的 GROUP_CONCAT 和 MATCH AGAINST 查询工作。缩短一点：
SELECT LOINC_NUM as ln,LONG_COMMON_NAME,SYSTEM,
(SELECT GROUP_CONCAT(PARENT_NAME) from panels WHERE LOINC_NUM = ln) as PANEL_STRING,
(SELECT GROUP_CONCAT(PARENT_LOINC) from panels WHERE LOINC_NUM = ln) as PANEL_LOINC_STRING,
MATCH (SYSTEM,LONG_COMMON_NAME)
AGAINST ('string' IN BOOLEAN MODE) AS score FROM loinc
WHERE MATCH(SYSTEM,LONG_COMMON_NAME)
AGAINST ('string' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 100
但是，如果我也想匹配 PANEL_STRING“字段”（这是一个 GROUP_CONCAT）。我在其他地方发现我可以将子查询更改为联接，但是这似乎只返回一行，因为并非 loinc 中的每一行都联接到面板。
有人愿意帮助我并向我展示如何使 GROUP_CONCAT 字段成为可用于 MATCH AGAINST 搜索的字段吗？谢谢
I have 2 tables: loinc and panels. I currently have a successful GROUP_CONCAT and MATCH AGAINST query working. Shortened a bit:
SELECT LOINC_NUM as ln,LONG_COMMON_NAME,SYSTEM,
(SELECT GROUP_CONCAT(PARENT_NAME) from panels WHERE LOINC_NUM = ln) as PANEL_STRING,
(SELECT GROUP_CONCAT(PARENT_LOINC) from panels WHERE LOINC_NUM = ln) as PANEL_LOINC_STRING,
MATCH (SYSTEM,LONG_COMMON_NAME)
AGAINST ('string' IN BOOLEAN MODE) AS score FROM loinc
WHERE MATCH(SYSTEM,LONG_COMMON_NAME)
AGAINST ('string' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 100
However, if I would also like to MATCH against the PANEL_STRING 'field' (which is a GROUP_CONCAT) as well. I have found elsewhere that I can change the subquery to a join, however that seems to be returning only a single line as not every line in loinc joins to a panel.
Anyone willing to help me out and show me how I may be able to make the GROUP_CONCAT field one that I can use for MATCH AGAINST searching? Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您的面板名称是层次结构 ID（如 001.007.0014），您可以创建一个以层次结构 ID 开头的 GROUP_CONCAT，然后在末尾添加 LOINC 编号或 LOINC 零件编号（如 001.007.014:LP7407-7 和 001.007） .014:9987-6)。使用 GROUP_CONCAT，您可以搜索面板的整个部分或仅搜索单个 LOINC 或 LOINC 部分。
If your panel name is the hierarchy ID (like 001.007.0014), you could create a GROUP_CONCAT that starts with the hierarchy ID, then adds the LOINC number or LOINC Part number at the end (like 001.007.014:LP7407-7 and 001.007.014:9987-6). With that GROUP_CONCAT, you can search on whole sections of panels or just the individual LOINC or LOINC Part.