现有两表A,B表A记录着使用者
字段
aUserID aName1 小李
2 小王
3 小赵表B记录着使用记录aUserID aMemo
1 ******
1 ******
2 ******
3 ******
1 ******
3 ******现求一SQL语句,可以列表统记每一个人的记录条数
如
小李 3
小王 1
小赵 2求最快速统计的方法,不采用存储过程select aName,(select count(*) from B where B.aUserID=A.aUserID) from A以上这条就不可采用了,因为数据量比较多,速度很慢,非常感谢.
字段
aUserID aName1 小李
2 小王
3 小赵表B记录着使用记录aUserID aMemo
1 ******
1 ******
2 ******
3 ******
1 ******
3 ******现求一SQL语句,可以列表统记每一个人的记录条数
如
小李 3
小王 1
小赵 2求最快速统计的方法,不采用存储过程select aName,(select count(*) from B where B.aUserID=A.aUserID) from A以上这条就不可采用了,因为数据量比较多,速度很慢,非常感谢.
A.aUserID,
B.cnt
FROM tb1 AS A
JOIN(
SELECT aUserID,COUNT(*) AS cnt
FROM tb2
GROUP BY aUserID
) AS B
ON A.aUserID=B.aUserID
from 表a a inner join 表b b on a.auserid=b.auserid
group by a.aname
A.aUserID,
COUNT(*) AS cnt
FROM tb1 AS A
JOIN tb2 AS B
ON A.aUserID=B.aUserID
GROUP BY A.aUserID
select A.aName,B.Num
from A,(
select aUserID,count(*) as NUm
from B
group by aUserID )B
where A.aUserID = B.aUserID B.aUserID 上加索引
from a , b
where B.aUserID=A.aUserID
group by a.aName
select aName,count(*)
from a , b
where B.aUserID=A.aUserID
group by a.aName
from table,
( select count(aUserID ) as num,aUserID
from table
group by aUserID ) AS A
where A.aUserID =table.aUserID
declare @表A table([aUserID] int,[aName] varchar(4))
insert @表A
select 1,'小李' union all
select 2,'小王' union all
select 3,'小赵'
declare @表B table([aUserID] int,[aMemo] varchar(6))
insert @表B
select 1,'******' union all
select 1,'******' union all
select 3,'******' union all
select 1,'******' union all
select 3,'******'select aName, count(B.aUserID) num
from @表A A
left join @表B B
on B.[aUserID]=A.[aUserID]
group by A.aName--测试结果:
/*
aName num
小李 3
小王 0
小赵 2
*/
from 表a a inner join 表b b on a.auserid=b.auserid
group by a.aname