select a.aId, a.name, isnull(count(b.aid),0) 统计 from a left join b on a.aId=b.aId group by a.aId,a.name
select a.aId,name,统计=count(1) from a,b where a.aId =b.aId group by a.aId,name
select t.*,(select count(*) from b where aId=t.aId) 统计 from a t --or: select t.*,m.统计 from a t left join (select aId,count(*) 统计 from b group by aId) m on t.aId=m.aId
CREATE TABLE A ( aId INT, name VARCHAR(5) ) INSERT INTO A SELECT 1,'牛' UNION ALL SELECT 2,'马' CREATE TABLE B ( aId INT, time INT ) INSERT INTO B SELECT 1,12 UNION ALL SELECT 1,3 UNION ALL SELECT 2,7 UNION ALL SELECT 1,9 SELECT A.aId,A.name,(SELECT COUNT(1) FROM B WHERE aId=A.aId) 统计 FROM A -- aId name 统计 ----------- ----- ----------- 1 牛 3 2 马 1(2 行受影响)
a.aId,
a.name,
isnull(count(b.aid),0) 统计
from
a
left join
b
on
a.aId=b.aId
group by
a.aId,a.name
from a t
--or:
select t.*,m.统计 from a t
left join
(select aId,count(*) 统计 from b group by aId) m
on t.aId=m.aId
(
aId INT,
name VARCHAR(5)
)
INSERT INTO A
SELECT 1,'牛' UNION ALL
SELECT 2,'马'
CREATE TABLE B
(
aId INT,
time INT
)
INSERT INTO B
SELECT 1,12 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,7 UNION ALL
SELECT 1,9
SELECT A.aId,A.name,(SELECT COUNT(1) FROM B WHERE aId=A.aId) 统计 FROM A
--
aId name 统计
----------- ----- -----------
1 牛 3
2 马 1(2 行受影响)