SELECT au.user_id, count(u.workno) AS tjs
FROM ecs_users AS u, ecs_admin_user AS au
WHERE au.workno = u.workno
AND au.action_list LIKE '%user_sales%'
GROUP BY au.user_id
结果是这样的:
user_id tjs
47 1
71 1
74 1
85 1
92 1
101 1 实际上还有另外两个user_id的,只是他们的count(u.workno)为0 但是这里没有显示出来。
我想让这两个也可以显示出来的。
例如:user_id tjs
33 0
68 0
FROM ecs_admin_user AS au LEFT JOIN ecs_users AS u
ON au.workno = u.workno AND au.action_list LIKE '%user_sales%'
GROUP BY au.user_id
那另一个表没有workno 为0的 当然得不到啊
用做连接试试 left join
SELECT
au.user_id,au.workno AS tjs
FROM
ecs_users AS u left join ecs_admin_user AS au
on
au.workno = u.workno
where
au.action_list LIKE '%user_sales%'
order BY
au.user_id,au.workno desc
admin:user_id,worknousers:user_id,worknoadmin中的workno和users中的workno是一对多的关系。打个比方就像是一个业务员,他名义下面肯定有1个或者多个的客户,当然也有可能某个业务员下面一个客户都没有。workno相当于是业务员的编号,比如users表中的workno=1,就表示属于admin表中workno=1的业务员的客户。现在就想获得每个业务员的编号,名义下面的客户数量。比如admin表的记录如下: user_id worko
2 1001
3 1003
5 1005
users表中的记录如下: user_id workno 7 1003
8 1005
9 1005我要实现的效果是: user_id(admin表的user_id) number(客户数量)
2 0
3 1
5 2
SELECT au.user_id, count(nvl(u.workno,0)) AS tjs
FROM ecs_users AS u, ecs_admin_user AS au
WHERE au.workno = u.workno
AND au.action_list LIKE '%user_sales%'
GROUP BY au.user_id
SELECT au.user_id, count(u.workno) AS tjs
FROM ecs_admin_user AS au LEFT JOIN ecs_users AS u
ON au.workno = u.workno GROUP BY au.user_id上面这个就已经达到你的要求的,
只是你又多加了一个模糊查询au.action_list LIKE '%user_sales%',对ecs_admin_user表的action_list字段进行模糊匹配user_sales(不知道为什么要去搜索user_sales,还是说这个是$user_sales变量)
SELECT au.user_id, count(u.workno) AS tjs
FROM ecs_admin_user AS au LEFT JOIN ecs_users AS u
ON au.workno = u.workno AND au.action_list LIKE '%user_sales%'
GROUP BY au.user_id