我有两个表:
A表主键为a_id作为外键连接到表B
比如现在A中数据是
A:
a_id name
1 cherry
2 miky
3 sweet
B表中的数据是
B:
b_id a_id
1 1
2 1
3 1
4 3
5 2
6 1
我想选取A中数据按表B中a_id出现次数的大小进行排序。这个怎么写呢。高手赐教!
A表主键为a_id作为外键连接到表B
比如现在A中数据是
A:
a_id name
1 cherry
2 miky
3 sweet
B表中的数据是
B:
b_id a_id
1 1
2 1
3 1
4 3
5 2
6 1
我想选取A中数据按表B中a_id出现次数的大小进行排序。这个怎么写呢。高手赐教!
select A.* from A not Exits (select * from B where A.id=B.id)然后在这个SQL的基础上用GROUP BY来取的他的值,select C.id from (select A.* from A not Exits (select * from B where A.id=B.id))
C group by C.a_id没有环境调试,不好意思哦,只能写个大概的思路了
CREATE TABLE #B(b_id INT,a_id INT);INSERT INTO #A( a_id, NAME )
SELECT 1,'cherry' UNION
SELECT 2,'miky' UNION
SELECT 3,'sweet'INSERT INTO #B( b_id, a_id )
SELECT 1,1 UNION
SELECT 2,1 UNION
SELECT 3,1 UNION
SELECT 4,3 UNION
SELECT 5,2 UNION
SELECT 6,1 UNION
SELECT 7,3SELECT a.a_id,#A.NAME FROM (SELECT a_id,COUNT(a_id) AS num FROM #B GROUP BY a_id ) AS a INNER JOIN #A
ON a.a_id = #A.a_id ORDER BY num DESC
from A,B
where A.a_id=B.a_id GROUP BY a_id,name order by a_id desc
from a,b
where a.a_id=b.a_id group by a.a_id,a.name
order by count(a.a_id) desc
from a,(select a_id,count(1) as cnt from b group by a_id) c
where
a.a_id=c.a_id
group by a.a_id,a.name
order by c.cnt desc