求条SQL语句(100分)

M

mylinc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #1

ACCESS有两张表

A表
AID NAME
1 XXX
2 XXX
3 XXX


B表
ID AID
1 2
2 2
3 2
4 3
5 2

B表的AID调用的是A表中的AID,两个字段一对多的关系，想查询A表中信息并按B表中AID被调用的个数查询。不知道SQL怎么写，请各位帮个忙

 

M

mylinc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #2

想查询A表中信息并按B表中AID被调用的总个数查询排列

 

S

szhcracker

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #3

不大明白你的意思，下面的是Oracle的写法，你参考参考：
Select a.* From A a
Left Join (Select AID, Count(AID) ca From B Group By AID Order By Count(AID)) b On a.AID = b.AID
Order By b.AID

 

M

mylinc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #4

就是说A表中的AID在B表中出现的记录越多 ，A表显示的时候该AID记录越靠前

 

S

szhcracker

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #5

Select a.* From A a
Left Join (Select AID, Count(AID) ca From B Group By AID Order By Count(AID) Desc) b On a.AID = b.AID
Order By b.ca Desc

 

M

mylinc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-29

• #6

能给条ACCESS的啊，Oracle看不懂

 

C

creation-zy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-30

• #7

经过试验，发现Access支持嵌套查询，可以这么干：

Select * From (
SELECT A.AID, A.NAME, Count(B.ID) AS C
FROM A LEFT JOIN B ON A.AID = B.AID
GROUP BY A.AID, A.NAME
)
Order By C

 

F

fnhkewfg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-30

• #8

select * from B left outer join A on B.AID=A.AID

 

H

huangli_nj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-30

• #9

select A.AID,A.Name from A left join B on A.AID=B.AID group by A.AID,A.Name order by count(B.AID) desc

 

M

mylinc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-10-31

• #10

多人接受答案了。