SELECT count(*) from apart as a join bgroup as b on a.gid = b.gid where b.gid = 1 ; apart是A表 里面有外联的gid字段 通过join on 然后就可以查到了 gid=1 就是组类别 你可以参考sql join
由于涉及到分组统计 再取值,所以得用视图或临时表这里以视图为例: 说明:表A: a 表BA: b 视图:tmp 以下代码本地验证通过DROP view IF EXISTS tmp ; create view tmp as SELECT aa.user, count( aa.user ) AS u, bb.id, bb.groupname, bb.groupuser FROM a AS aa, b AS bb WHERE LOCATE( aa.user, bb.groupuser ) !=0 GROUP BY aa.user ORDER BY bb.id ASC; SELECT id, groupname, REPLACE( groupuser, user, CONCAT( user, '(', u, ')' ) ) AS guser from tmp ;
还没结贴啊? 算了再写个临时表的create temporary table tmp as SELECT aa.user, count( aa.user ) AS u, bb.id, bb.groupname, bb.groupuser FROM a AS aa, b AS bb WHERE LOCATE( aa.user, bb.groupuser ) !=0 GROUP BY aa.user ORDER BY bb.id ASC; SELECT id, groupname, REPLACE( groupuser, user, CONCAT( user, '(', u, ')' ) ) AS guser from tmp ;
apart是A表 里面有外联的gid字段
通过join on 然后就可以查到了 gid=1 就是组类别 你可以参考sql join
说明:表A: a 表BA: b 视图:tmp
以下代码本地验证通过DROP view IF EXISTS tmp ;
create view tmp as
SELECT aa.user, count( aa.user ) AS u, bb.id, bb.groupname, bb.groupuser
FROM a AS aa, b AS bb
WHERE LOCATE( aa.user, bb.groupuser ) !=0
GROUP BY aa.user
ORDER BY bb.id ASC;
SELECT id, groupname, REPLACE( groupuser, user, CONCAT( user, '(', u, ')' ) ) AS guser from tmp ;
SELECT aa.user, count( aa.user ) AS u, bb.id, bb.groupname, bb.groupuser
FROM a AS aa, b AS bb
WHERE LOCATE( aa.user, bb.groupuser ) !=0
GROUP BY aa.user
ORDER BY bb.id ASC;
SELECT id, groupname, REPLACE( groupuser, user, CONCAT( user, '(', u, ')' ) ) AS guser from tmp ;