select table1.bookname, table1.bookid, t.cnt from table1, (select bookid, count(*) as cnt from table2 group by bookid) as t where table1.bookid = t.bookid
如果你是用VB作平台开发,则应执行如下操作就可以: 第一个表(图书名,图书id,借出次数) 第二表(图书id,借出时间,借书人id) dim book_count as string adodc1.recordsource="select * from table1 " adodc1.reford do while not adodc1.recordset.eof book_count=adodc1.recordset.fields("bookid") adodc2.recordource="select * from table2 where bookid='"+book_count+"'" adodc2.reford adodc1.recordset.fields("借出次数")=adodc2.recordset.recordcount adodc1.recordset.update adodc1.recordset.movenext loop adodc1.recordsouce="select * from table1 order 借出次数" adodc1.refcord
access库的话,第二种方法是不是效率很低呢
select * from 表1 left join select 图书id,count(借出时间) from 表2 group by 图书id on 表1图书id = 表2 图书id以上查询已在脑海中yi~yin了两遍,请施主放心使用。
from table1,
(select bookid, count(*) as cnt from table2 group by bookid) as t
where table1.bookid = t.bookid
第一个表(图书名,图书id,借出次数)
第二表(图书id,借出时间,借书人id)
dim book_count as string
adodc1.recordsource="select * from table1 "
adodc1.reford
do while not adodc1.recordset.eof
book_count=adodc1.recordset.fields("bookid")
adodc2.recordource="select * from table2 where bookid='"+book_count+"'"
adodc2.reford
adodc1.recordset.fields("借出次数")=adodc2.recordset.recordcount
adodc1.recordset.update
adodc1.recordset.movenext
loop
adodc1.recordsouce="select * from table1 order 借出次数"
adodc1.refcord