MySQL加入LIKE语句 [英] MySQL Join on LIKE statement

问题描述

我需要计算数据库中每个组中有多少用户.不幸的是，数据库设计不是很好，并且用户uid是针对该组存储在LONGTEXT字段列名称owncloudusers中的组表中的. owncloudusers数据示例:

I need to count how many users are in each group in a database. Unfortunately the database design is not great and the users uids are stored against the group in the group table in a LONGTEXT field column name owncloudusers. Example of owncloudusers data :

{i:0; s:36:"25C967BD-AF78-4671-88DC-FAD935FF1B26"; i:1; s:36:"40D6866B-EA06-4F39-B509-8CE551CC1924"; i:2; s: 36:"7724C600-DE23-45C8-8BFD-326B0138E029​​"; i:3; s:36:"D6FF37EC-11F4-471F-94C9-F3A28416CF1F"; i:4; s:36:"F70C6D03-B7BA-44E4-B703 -9AF3EED9BC03;}

{i:0;s:36:"25C967BD-AF78-4671-88DC-FAD935FF1B26";i:1;s:36:"40D6866B-EA06-4F39-B509-8CE551CC1924";i:2;s:36:"7724C600-DE23-45C8-8BFD-326B0138E029";i:3;s:36:"D6FF37EC-11F4-471F-94C9-F3A28416CF1F";i:4;s:36:"F70C6D03-B7BA-44E4-B703-9AF3EED9BC03";}

我以为我可以在联接上使用带有LIKE的查询来比较用户的uid，并查看owncloudusers内是否存在匹配项.

I thought I could use a query with a LIKE on the join to compare the user's uid and look inside owncloudusers and see if there is a match.

我最近的是:

SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
FROM oc_ldap_group_members T1
LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE('%:"'||T2.owncloud_name||'";%') 
GROUP BY owncloudname;

T1表保存分组以及谁被标记到该组 T2表保存用户数据.列owncloud_name是用户uid列

T1 table holds the groupings and who is tagged to that group T2 table holds the users data. column owncloud_name is the users uid column

我尝试了一些在LIKE join和LIKE('％:'+ T2.owncloud_name +'";％')上的stackoverflow CONCAT上找到的方法

I have tried a few approaches I found on stackoverflow CONCAT on the LIKE join and LIKE('%:"'+T2.owncloud_name+'";%')

但是没有喜悦.我当前的语句在所有组中返回0个用户，但我知道这是不对的.

But no joy. The current statement I have returns 0 users against all the groups but I know this is not right.

我很了解它，但是关于联接的问题不确定下一步要去哪里.

I know it much but an issue on the join not sure where to go with it next.

任何帮助将不胜感激.

推荐答案

我认为您需要一个简单的

I think you need a simple

    SELECT T1.owncloudname, count(*) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE '%T2.owncloud_name%' 
    GROUP BY owncloudname;

如果需要concat，请尝试

If you need concat try

    SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers 
       LIKE concat( '%',T2.owncloud_name,'%' )
    GROUP BY owncloudname;

这篇关于MySQL加入LIKE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！