请教一个SQL语句问题

[pigleter]

在Access中有个表
ID Name
1 a
1 b
2 c
2 d
2 e
3 f

如何实现
ID Name Count
1 a 2
1 b 2
2 c 3
2 d 3
2 e 3
3 f 1

[OracleRoob]

SELECT A.ID, A.name,(select count(*) from 表名 where id=A.id) as [Count]
FROM 表名 AS A;

[lllyyymmm]

select id,name,count=(select count(*) from table b where b.id=a.id ) from table a

[OracleRoob]

--如果ID是字符型：

SELECT A.ID, A.name,dcount("ID","表名","id='" & A.id & "'") as [Count]
FROM 表名 AS A;


--如果ID是数值型：

SELECT A.ID, A.name,dcount("ID","表名","id=" & A.id) as [Count]
FROM 表名 AS A;

[OracleRoob]

--用子查询
SELECT A.ID, A.name,(select count(*) from 表名 where id=A.id) as [Count]
FROM 表名 AS A;


--或用域函数

--如果ID是字符型：

SELECT A.ID, A.name,dcount("ID","表名","id='" & A.id & "'") as [Count]
FROM 表名 AS A;


--如果ID是数值型：

SELECT A.ID, A.name,dcount("ID","表名","id=" & A.id) as [Count]
FROM 表名 AS A;

[vbman2003]

try

select a.[ID],[Name],cnt
from tb a left join (select [id],count([id]) as cnt from tb group by [id]) b
on a.[id]=b.[id]

[wwwwb]

用内、外连接效率高一些
select a.[ID],[Name],cnt
from tb a inner join (select [id],count([id]) as cnt from tb group by [id]) b
on a.[id]=b.[id]