现有两个表A和B,A.id 与B.aid关联,一个A对应多个B,B里面有一个关键字是记录顺序的B.order,这个时候我需要联合查询A和B,得到每个A有多少个B,关键是必须按照B.order得到这个值,我的语句如下select count(A.id) as newid where A.id=B.aid group by A.id order by B.order但是提示说B.order不再聚合函数里,也不在groupby子句中,很郁闷,在线等待高手指点。
调试欢乐多
a表数据
1
2b表数据
order aid
1 1
2 2
3 2
4 1如果这样的话,那应该按照什么排序呢?
楼主想要的结果是什么呢?
from A
join B on A.id = B.aid
group by A.id或者
select A.id,count(A.id) as newid
from A,B where A.id=B.aid group by A.id 另外,关键是必须按照B.order得到这个值 什么意思?
from A inner join B on A.id=B.aid
group by A.id
order by min(B.[order])
A
id
1
2B
id aid order
a 1 1
b 1 2
c 2 3
d 2 4也就是说,在B中只要aid一样,那么order必然是连续的
from A inner join B on A.id=B.aid
group by A.id
order by min(B.[order])
?
表的规则如下
A
id
1
2B
id aid order
a 1 1
b 1 2
c 2 3
d 2 4
create table a
(
id int
)create table b
(
id varchar(1),
aid int,
[order] int
)
insert into a select 1
insert into a select 2insert into b select 'a',1,1
insert into b select 'b',1,2
insert into b select 'c',2,3
insert into b select 'd',2,4
select a.id ,count(*) as [newid]
from a inner join b on a.id=b.aid
group by a.id
order by min(b.[order])--结果
1 2
2 2
from A inner join B on A.id=B.aid
group by A.id
order by min(B.[order])