简单问题:求一sql查询表达式.立即结帖

[xishanlang2001]

现在有
表A如下:
id a
1 100
2 200
表B如下:
id b
1 100
2 100
3 200
4 200
5 200

现在想要一sql查询表达式,查询所有表A中的纪录,按表A中字段a的值在表B中字段b出现次数排序.在打印时要求打印a的值
我试了
select A.a,count(B.b) from A,B ......
可是提示说,如果用了count,必须全用.像这样:count(A.a),count(B.b)

请问各位大侠,怎么办呀?

[ggyy9494]

不会,帮你UP一下

[xishanlang2001]

UP,在线等,立即结帖

[xishanlang2001]

这里这么多高手啊.
谢谢,谢谢

[xishanlang2001]

谢谢,谢谢

[jacklinchen]

select a,count(B.b) bc from A,B where a=b group by a order by bc desc 试试

[monkeys]

select A.a from A order by (select count count(b) as str1 from B) desc

[bananasmiling]

select t1.a ,count(t2.b)b from a t1,b t2 order by t2.b

[leiaming]

select A.a,count(B.b) as sum from A,B where A.a=B.b group by A.a order by sum desc

[bluesmile979]

这个应该没问题的

select A.a from A order by (select count count(b) as str1 from B) desc

[xishanlang2001]

to leiaming(630178

set conn=server.createobject("adodb.connection")
conn.open "DBQ="& Server.MapPath("../db/test.mdb") &";Driver={Microsoft Access Driver (*.mdb)}"
set rs=server.createobject("adodb.recordset")
sql="select a.a,count(b.b) as sum from a,b where a.a=b.b group by a.a order by sum desc"
' sql="select a1.*,count(b1.*) from a a1,b b1 where a1.a=b1.b"
' sql="select a1.a from a a1,b b1 where (a1.a='100' or a1.a='200') ORDER BY ()"
' sql="select count(b) from b where b='100'"
'' sql="select a.*,b.* from a,b where a.id=2 and b.id=3"
rs.open sql,conn,1,3
错误类型：
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] 参数不足，期待是 1。
说最后一行有错.呵呵,怎么办?

[xishanlang2001]

bluesmile979(笑着) (

错误类型：
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] 语法错误。 在查询表达式 '(select count(b) as str1 from B)' 中
但我试过,select count(b) as str1 from B是没有错的.奇怪.
请大家指点!

[xishanlang2001]

还是不行,请各位大侠指点!