在ASP版用史上最蠢的方法回答了一个SQL的问题,郁闷ing,现在来SQL版求正解!

[wfront]

问题大概是这样的:
有两个表books,categories.
categories用来存储图书类别有ID,category两个字段.
books用来存储图书信息,主要用到的有三个字段book_id,Book_name,categoryid.其中categoryid是表categories中ID字段的外键.
现在要求的是在每种图书类别中各求一本书的信息,order by book_id desc

以下是史上最蠢的解决方法:

<%
dim rs1,rs2
dim sql1,sql2,sql3
dim i

set rs1 =server.CreateObject("ADODB.recordset")
sql1="select id from categories order by id"
rs1.Open sql1,conn,3,1

sql2="select top 1 book_id,Book_name,categoryid from books where categoryid=" & rs1("id")
set rs2 =server.CreateObject("ADODB.recordset")

for i=1 to rs1.RecordCount -1
rs1.MoveNext
sql2=sql2 & " union select top 1 book_id,Book_name,categoryid from books where categoryid=" & rs1("id")
next
sql2=sql2 & " order by book_id desc"

rs1.Close
set rs1=nothing
rs2.Open sql2,conn,3,1

%>


求正解!

[tony0115]

select
a.*,b.*
from
categories a,
(
select book_id,Book_name,categoryid
from books
where book_id in (select max(book_id) from books group by categoryid )
) b
where
a.id=b.categoryid

[phantomMan]

select min(book_id),book_name,categoryid from books
inner join categories on books.categoryid=categories.id
group by categoryid
order by book_id desc