高分100求一简单SQL语句

[523shan]

现在有两个表A，B，A表中的ID和B表中的ID是一样的
A表
---------------------
ID tit
1 aa
2 bb
3 cc
4 dd

---------------------
B表
-----------------------
BID ID btit
1 1 qq
2 2 ee
3 1 rr
4 1 rt
5 2 yy
6 4 uu
---------------------------

现在想得到下面的查询结果
--------------------
ID tit 次数
1 aa 3
2 bb 2
4 dd 1
3 cc 0
---------
就是查询后得到A表的ID和tit，以及ID在B表中出现的次数，并按照次数排序
谢谢各位大大！注意一下，我用的是ACCESS2000数据库

[vivianfdlpw]

至少一个参数没有被指定值
==========>
这是你的.Net程序中的错误,没有为参数赋值

[523shan]

.NET程序应该不会出错，我这是一个datalist
我用
select ID,count(a.ID) as 次数 from A inner join B on A.ID=B.ID group by ID
这个SQL语句时候能正常运行，统计出次数
可是加上tit就出错

[lxzm1001]

select a.id,a.tit,次数=count(*) from a,b where a.id=b.id group by a.id,a.tit order by 次数 desc

[xiaoxiangqing]

select A.*,次数=(select count(*) from B where A.ID=ID) from A

[xiaoxiangqing]

/*
.NET程序应该不会出错，我这是一个datalist
我用
select ID,count(a.ID) as 次数 from A inner join B on A.ID=B.ID group by ID
这个SQL语句时候能正常运行，统计出次数
可是加上tit就出错
/*
--改为下列:
select ID,max(tit) tit,isnull(count(a.ID),0) as 次数 from A left join B on A.ID=B.ID group by A.ID

[$扫地僧$]

我在 Access2000 中测试个没问题啊！
就是 3 的那行 不能显示 0 我对Access2000 不是很熟
只有看你能能将 3 那行补个 0 应该就可以哦！！

[$扫地僧$]

这个 错误提示 大概指的就是那行啊！！

[523shan]

谢谢各位老大啦，我再试试

[qiqingshizhe]

declare @a table(id int,tit varchar(20))
insert @a select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'
declare @b table(bid int,id int,tit varchar(20))
insert @b select 1,1,'qq'
union all select 2,2,'ee'
union all select 3,1,'rr'
union all select 4,1,'rt'
union all select 5,2,'yy'
union all select 6,4,'uu'
select a.[id],a.tit,[次数]=isnull(b.[次数],0)
from(
select [id]=id,tit from @a
)a
left join(
select [id]=id,[次数]=count(*) from @b group by id)b
on a.[id]=b.[id]
order by [次数] desc

[qiqingshizhe]

declare @a table(id int,tit varchar(20))
insert @a select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'
declare @b table(bid int,id int,tit varchar(20))
insert @b select 1,1,'qq'
union all select 2,2,'ee'
union all select 3,1,'rr'
union all select 4,1,'rt'
union all select 5,2,'yy'
union all select 6,4,'uu'
select a.[id],a.tit,[次数]=isnull(b.[次数],0)
from(
select [id]=id,tit from @a
)a
left join(
select [id]=id,[次数]=count(*) from @b group by id)b
on a.[id]=b.[id]
order by [次数] desc

[wangkenping]

--我在Access2000 中测试中通过.
create table a (id integer,tit varchar(20));
insert into a (id,tit) select 1,'aa';
insert into a (id,tit) select 2,'bb';
insert into a (id,tit) select 3,'cc';
insert into a (id,tit) select 4,'dd';
create table b (bid integer,id integer,tit varchar(20));
insert into b (bid,id,tit) select 1,1,'qq';
insert into b (bid,id,tit) select 2,2,'ee';
insert into b (bid,id,tit) select 3,1,'rr';
insert into b (bid,id,tit) select 4,1,'rt';
insert into b (bid,id,tit) select 5,2,'yy';
insert into b (bid,id,tit) select 6,4,'uu';


--方法一 派生表
select a.id,
a.tit,
t.sl as 次数
from a,(select b.id,count(b.id) as sl from b group by b.id) t
where a.id=t.id;
--方法二
select a.id,a.tit,count(a.id) as 次数
from a,b
where a.id=b.id
group by a.id,a.tit;


drop table a;
drop table b;

[wangkenping]

注意Access2000
1.在不标题不能用这中方法 select a.[id],a.tit,[次数]=isnull(b.[次数],0)
只有这样select a.[id],a.tit,isnull(b.[次数],0) as [次数]

2.插入数据时要用insert into a (id,tit) select 1,'aa',用insert into a select 1,'aa'
不行
3.不支持表变量和临时表

[funsuzhou]

declare @a table(id int,tit varchar(20))
insert into @a
select 1, 'aa'union all
select 2, 'bb'union all
select 3, 'cc'union all
select 4, 'dd'
declare @b table(bid int,id int,btit varchar(20))
insert into @b
select 1, 1, 'qq'union all
select 2, 2, 'ee'union all
select 3, 1, 'rr'union all
select 4, 1, 'rt'union all
select 5, 2, 'yy'union all
select 6, 4, 'uu'

select a.[id],a.[tit],isnull(b.次数,0)as 次数 from @a a left join(
select [id],count([id])as 次数 from @b group by [id])b
on a.[id]=b.[id]
order by b.[次数] desc

/*
id tit 次数
----------- -------------------- -----------
1 aa 3
2 bb 2
4 dd 1
3 cc 0

（所影响的行数为 4 行）
*/

[feng1071]

仅仅是一种方法而已!!
select A.ID,Y.X,A.TIT
FROM A,
(select A.ID ,count(cd.tit) X
from A left join(
select A.id,A.tit,b.bid,b.btit
from A,B
where A.ID=b.ID)cd
on A.ID=cd.ID
group by A.ID)Y
WHERE A.ID=Y.ID
ORDER BY Y.X DESC

[yongwin]

试下我这个吧，SQL里好用，就不知道ACCESS2000好用不（仅供参考）
select min(A.ID)as ID,min(tit)as tit,sum(
case A.ID when B.ID then 1 else 0 end)as uu
from A,B
group by A.ID
order by uu desc

[vovo2000]

select
a.ID,a.tit,(select count(b.ID)from b where b.id=a.id) as 次数
from
A left join B
on
A.ID=B.ID group by a.ID, a.tit

[523shan]

谢谢各位老大的热情帮助，结分了

[Kassis78]

直接用我的就行了。
select a.id,a.tit,b.次数 from a left join
(select a.id,count(b.id) as 次数 from B right join a on a.id=b.id group by a.id) as b on a.id=b.id order by b.次数 desc

[Kassis78]

我无分么？

[523shan]

谢谢各位老大的热情帮助，结分了

---------------------
我发了这条就去结分了，当时没看到你的回复啊