SQL 问题,LEFT JOIN [..] IN()
这个小小的 SQL 错误困扰着我。 查询似乎没有问题,只是范围(?),示例效果最好:
SELECT ocp.*, oc.*, GROUP_CONCAT( u.username SEPARATOR ', ') AS `memjoined`
FROM gangs_ocs_process ocp, gangs_ocs oc
LEFT JOIN users u ON u.userid IN ( ocp.membersin )
WHERE ocp.ocid =1 AND ocp.gangid =1 AND oc.oc_name = ocp.crimename
GROUP BY ocp.ocid
LIMIT 0 , 30
有一列(gangs_ocs_process.membersin),其中包含已加入的ID列表(即1,2,5)。 我试图一次性获取每个 ID 的用户名(从 users
表中)。
问题是 LEFT JOIN users u ON u.userid IN ( ocp.membersin )
如果我用 1,2,4
替换 ocp.membersin
>(放置文字列表而不是列名),它工作正常。 它返回一个包含用户名的列(image)。 但是,如果我留在 ocp.membersin 中,则会收到此错误:
#1054 - Unknown column 'ocp.membersin' in 'on Clause'
这是我第一次在左连接所以我有点迷失。
任何帮助都会很棒:)
This small SQL error is bugging me. It doesn't seem to be a problem with the query, just the scope(?), examples work best:
SELECT ocp.*, oc.*, GROUP_CONCAT( u.username SEPARATOR ', ') AS `memjoined`
FROM gangs_ocs_process ocp, gangs_ocs oc
LEFT JOIN users u ON u.userid IN ( ocp.membersin )
WHERE ocp.ocid =1 AND ocp.gangid =1 AND oc.oc_name = ocp.crimename
GROUP BY ocp.ocid
LIMIT 0 , 30
Theres a column (gangs_ocs_process.membersin) which has a list of IDs that have joined (ie 1,2,5). I'm trying to get the usernames for each of these IDs (from the users
table) in one go.
The problem is LEFT JOIN users u ON u.userid IN ( ocp.membersin )
If I substitue 1,2,4
in for ocp.membersin
(putting the literal list instead of column name), it works ok. It returns a column that has the usernames (image). However, if I leave in the ocp.membersin, I get this error:
#1054 - Unknown column 'ocp.membersin' in 'on clause'
This is the first time I've even used IN in left joins so I'm a bit lost.
Any help would be great :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
我认为“IN”不适用于这种语法。 MySQL 期望 IN 类似于数据集,而不是分隔字符串。 我认为你需要找到一种方法来获取成员,将其扩展为 MySQL 可以使用的数据集(可能是临时表),然后加入其中。
I don't think that "IN" will work for this syntax. MySQL expects IN to be something akin to a dataset, not a delimited string. I think you need to find a way to take membersin, expand it into a dataset MySQL can work with (maybe a temporary table), and join on that.
如果表中存在分隔字符串,则数据库中存在设计问题。 添加一个新表来保存这些值。
If you have delimited strings in your table, you have a design problem in your database. Add a new table to hold these values.
您确定“membersin”位于“gangs_ocs_process”表中,而不是“gangs_ocs”表中吗?
Are you sure 'membersin' is in the 'gangs_ocs_process' table, and not the 'gangs_ocs' table?
您无法让它工作的原因是因为首先您需要对数据库进行标准化。 您永远、永远都不应该在单个列中包含 ID 列表。
The reason you can't get it to work is because first you need to get your database NORMALIZED. You should NEVER, EVER have a list of ID's in a single column.
再看一遍之后,我认为您的问题是尝试在错误的点以及 IN 语法上进行聚合,并且您应该在受 IN 内容限制的子查询中进行聚合。 我对你的架构了解不够,无法使其开箱即用正确,但你想要这样的东西。 SomeKeyfield 应该与 gangs_ocs_process 相关
After taking another look, I think your problem is trying to aggregate at the wrong point as well as the IN syntax and that you should aggregate in a subquery restricted by the contents of the IN. I don't know enough about your schema to make this out of the box correct, but you want something like this. SomeKeyfield should relate back to gangs_ocs_process
这是保持会员资格的坏方法。
但如果您仍然需要忍受它,您可以尝试
REGEXP
匹配来测试成员资格:This is a bad way to keep membership.
But if you still need to live with it, you may try
REGEXP
matching to test for membership: