xt
发帖数: 17532 | 1 我现在有2个table:
A: id INTEGER PRIMARY KEY,
B:... Aid INTEGER,
假设B里面的Aid是个foreign key指向A.我现在想找出A里面的row
但按照row里面的id在B里面出现的次数多少排序.怎么做?
Thanks. | B**z
发帖数: 153 | 2 try try this,
select * from A where id in
(
select Aid from B group by Aid
)
order by
(select count(*) as ct from B where
B.Aid=A.id)
desc
【在 xt 的大作中提到】
: 我现在有2个table:
: A: id INTEGER PRIMARY KEY,
: B:... Aid INTEGER,
: 假设B里面的Aid是个foreign key指向A.我现在想找出A里面的row
: 但按照row里面的id在B里面出现的次数多少排序.怎么做?
: Thanks.
| k*******d
发帖数: 237 | 3 Select A.*, count(*) as ShowTimes
from A join B on A.id=B.Aid
group by A.*
order by ShowTimes desc
【在 B**z 的大作中提到】
: try try this,
: select * from A where id in
: (
: select Aid from B group by Aid
: )
: order by
: (select count(*) as ct from B where
: B.Aid=A.id)
: desc
| B**z
发帖数: 153 | 4 your query returns one extra field in the resulset.
【在 k*******d 的大作中提到】
: Select A.*, count(*) as ShowTimes
: from A join B on A.id=B.Aid
: group by A.*
: order by ShowTimes desc
|
|
