排序问题(又开一帖)

[不耐烦]

刚才那帖已经结贴了~有点乱重新来一次~~麻烦了
表a
id title
1 e1
2 e2
表b
id a_id hit
1 1 4
2 1 5
3 2 20
4 2 3

 <%
 set rs=conn.execute("select * from [a]")
 do while not rs.eof
 set rrrr=conn.execute("select sum(hit) as zhongfen from [b] where a_id="&rs("id"))'总和
 response.Write""&rs(title)&":"&rrrr("zhongfen")&"<br />"
 rs.movenext
 %>

得出结果是：
e1:9
e2:23
如何让表a 里的title 按照 (表b里a_id等于表a里的id 总和大小排列)
也就是结果是
e2:23
e1:9

谢谢了

[jhwcd]

 select tablea.title,sum(tableb.hit)as hit from  a tablea INNER JOIN b tableb ON tablea.id = tableb.a_id GROUP BY tablea.title order by hit desc

[jingxiaoping]

假设tt1是表a，tt2是表b

SELECT DISTINCT a.title, SUM(b.hit) AS expr1
FROM tt1 a INNER JOIN
      tt2 b ON a.id = b.a_id
GROUP BY a.title
ORDER BY a.title DESC

[jingxiaoping]

DISTINCT去掉也行，刚才顺手加上的。

[gingerkang]

set rs=conn.execute("select a.title,sum(b.hit) as zhongfen from a inner join b on a.id=b.a_id group by a.id,a.title order by sum(b.hit) desc")
do while not rs.eof
response.write rs(0) &":"& rs(1) &"<br>"
rs.movenext
loop
rs.close

[jingxiaoping]

再改。

SELECT DISTINCT a.title, SUM(b.hit) AS expr1
FROM tt1 a INNER JOIN
      tt2 b ON a.id = b.a_id
GROUP BY a.title
ORDER BY expr1 DESC

[jhwcd]

[Quote=引用 4 楼 jingxiaoping 的回复:]
再改。
VBScript codeSELECTDISTINCT a.title, SUM(b.hit)ASexpr1
FROM tt1 a INNERJOINtt2 bONa.id=b.a_id
GROUP BY a.title
ORDER BY expr1 DESC
[/Quote]