MYSQL如何一条语句取相应的字段

显示全部楼层 · 2010-8-12 10:43:57

例如books表存图书的基本信息，member表存借取该图书的会员。
请问如何一次取出某本图书的基本信息bookid,bookname,author 和借走该图书的会员人数。

千问 · 2010-8-12 10:43:57

为避免同一本书被同一个人借走很多次的情况：select t1.bookId,t1.bookname,t1.author,t2.count from books as t1,(select bookId,count(1) as count from (select bookId,memberId from member group by bookId,memberId) as t group by bookId) as t2 where t1.bookId=t2.bookId当然如果不在乎这个，一楼仁兄的即可

千问 · 2010-8-12 10:43:57

mysql下的话,用group by很简单就能实现:select t1.bookid,t1.bookname,t1.author,count(*) as 借走该图书的会员人数from books t1, member t2where t1.bookid=t2.bookidgroupo by t1.bookid

千问 · 2010-8-12 10:43:57

select b.bookid,b.bookname,b.author,t.num from books b,(select bookid,count(1) num from member group by bookid) twhere b.bookid=t.bookid