用户表:
id username
001 user1
002 user2
003 user3
处理表:
id userid
1 001
2 002
3 001
4 001
5 002
6 001
统计用户已处理数,想得到如下结果:
用户名 处理数目
user1 4
user2 2
user3 0请问SQL语句应该怎么写?
id username
001 user1
002 user2
003 user3
处理表:
id userid
1 001
2 002
3 001
4 001
5 002
6 001
统计用户已处理数,想得到如下结果:
用户名 处理数目
user1 4
user2 2
user3 0请问SQL语句应该怎么写?
select u.* from 用户表 u inner join 处理表 c on u.id=c.userid)t
group by username
a.username,
ISNULL(b.处理数目,0) AS 处理数目
FROM 用户表 AS a
LEFT JOIN(
SELECT
userid,
COUNT(*) AS 处理数目
FROM 处理表
GROUP BY userid
) AS b
ON a.id=b.userid
select u.* from 用户表 u left join 处理表 c on u.id=c.userid)t
group by username
select username,count(*) as num
from usertable as a left join detail as b on a.id=b.id
group by username
left join (select userid , count(1) cnt from 处理表 group by userid) n
on m.id = n.userid
username as 用户名,
处理数目=(select count(*) from 处理表 b where b.userid =a.id)
from 用户表 a
from 用户表 left outer join
(select count(userid ) as 处理数目 , userid
from 处理表
group by userid)
on (用户表.id= AA.id)
CREATE TABLE tb_a (id varchar(10) ,userName varchar(10))
INSERT INTO tb_a
SELECT '001','user1' UNION ALL
SELECT '002','user2' UNION ALL
SELECT '003','user3' CREATE TABLE tb_b (id INT ,userID varchar(10))
INSERT INTO tb_b
SELECT '1','001' UNION ALL
SELECT '2','002' UNION ALL
SELECT '3','001' UNION ALL
SELECT '4','001' UNION ALL
SELECT '5','002' UNION ALL
SELECT '6','001' SELECT
ta.userName
,Num=(SELECT count(*) FROM tb_b tb WHERE tb.userID=ta.id)
FROM tb_a tauserName Num
---------- -----------
user1 4
user2 2
user3 0(3 行受影响)
select username,处理数目=(select count(*) from 处理表 where id=用户表.id) from 用户表