Acces不知道行不行,你试下:
--try
select
A.ID,
A.tit
count(*)
from
A,B
where A.ID=B.ID
group by
A.ID,A.tit
order by
count(*) desc
--try
select
A.ID,
A.tit
count(*)
from
A,B
where A.ID=B.ID
group by
A.ID,A.tit
order by
count(*) desc
不行啊,提示错误:异常详细信息: System.Data.OleDb.OleDbException: 至少一个参数没有被指定值。
A表 LEFT OUTER JOIN (select id,count(*) as 次数 from B表 group by id ) T on A表.Id=T.ID
可还是不行啊,提示错误:至少一个参数没有被指定值。
==========>
这是你的.Net程序中的错误,没有为参数赋值
我用
select ID,count(a.ID) as 次数 from A inner join B on A.ID=B.ID group by ID
这个SQL语句时候能正常运行,统计出次数
可是加上tit就出错
.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
就是 3 的那行 不能显示 0 我对Access2000 不是很熟
只有看你能能将 3 那行补个 0 应该就可以哦!!
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
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
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;
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.不支持表变量和临时表
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 行)
*/
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
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
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
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