最近日期对应问题，求指点
TableA
bookid bookname
1 name1
2 name2
3 name3
4 name4
TableB
id dates sumNumber
1 2013-07-01 2
2 2013-07-01 1
3 2013-07-02 3
4 2013-07-04 2
TableC
id bid bookid values
1 1 1 0.4
2 1 2 0.3
3 2 3 0.5
4 3 1 0.4
5 3 2 0.4
6 3 3 0.6
7 4 1 0.4
8 4 2 0.45
A表是商品表，B表相当于订单主单C表是详细记录
求最近日期销售的商品A对应的values，不要游标。
结果：
bookid bookname dates values
1 name1 2013-07-04 0.4
2 name2 2013-07-04 0.45
3 name1 2013-07-02 0.6
请高手指点。 sql疑难问题
[解决办法]
SELECT
A.bookID,
A.bookName,
T.Dates,
T.[Values]
FROM tableA A
CROSS APPLY
(
SELECT TOP(1) B.dates,C.bookid,C.[Values],C.bid
FROM tableC C
INNER JOIN tableB B
ON C.bid = B.id
WHERE C.bookid = A.bookID
ORDER BY B.dates DESC, C.id DESC
) T
Declare @TableA table( boodid int, bookName varchar(50))
insert @TableA
select1, 'name1'union all
select2, 'name2'union all
select3, 'name3'union all
select4, 'name4'
Declare @TableB table(id int , dates datetime ,sumNumber int)
insert @TableB
select 1,'2013-07-01', 2 union all
select 2,'2013-07-01', 1union all
select 3,'2013-07-02', 3union all
select 4,'2013-07-04', 2
Declare @TableC table(id int , bid int ,bookid int, value decimal(18,2))
insert @TableC
select 1, 1, 1, 0.4 union all
select 2, 1, 2, 0.3union all
select 3, 2, 3, 0.5union all
select 4, 3, 1, 0.4union all
select 5, 3, 2, 0.4union all
select 6, 3, 3, 0.6union all
select 7, 4, 1, 0.4union all
select 8, 4, 2, 0.45
SELECT
A.boodid,
A.bookName,
T.Dates,
T.[value]
FROM @TableA A
CROSS APPLY
(
SELECT TOP(1) B.dates,C.bookid,C.[value],C.bid
FROM @TableC C
INNER JOIN @TableB B
ON C.bid = B.id
WHERE C.bookid = A.boodid
ORDER BY B.dates DESC, C.id DESC
) T
/*
boodidbookNameDatesvalue
1name12013-07-04 00:00:00.0000.40
2name22013-07-04 00:00:00.0000.45
3name32013-07-02 00:00:00.0000.60
*/
Declare @TableA table( bookid int, bookName varchar(50))
insert @TableA
select 1, 'name1' union all
select 2, 'name2' union all
select 3, 'name3' union all
select 4, 'name4'
Declare @TableB table(id int , dates datetime ,sumNumber int)
insert @TableB
select 1,'2013-07-01', 2 union all
select 2,'2013-07-01', 1 union all
select 3,'2013-07-02', 3 union all
select 4,'2013-07-04', 2 union all
select 5,'2013-07-04', 2
Declare @TableC table(id int , bid int ,bookid int, [values] decimal(18,2))
insert @TableC
select 1, 1, 1, 0.4 union all
select 2, 1, 2, 0.3 union all
select 3, 2, 3, 0.5 union all
select 4, 3, 1, 0.4 union all
select 5, 3, 2, 0.4 union all
select 6, 3, 3, 0.6 union all
select 7, 4, 1, 0.4 union all
select 8, 4, 2, 0.45 union all
select 9, 5, 2, 0.45 union all
select 10, 5, 3, 0.45
--2000的话没有什么好办法
SELECT
A.bookID,
A.bookName,
d.Dates,
d.[Values]
FROM @tablea A
INNER JOIN--获得每个book的最大日期
(
SELECT C.bookid,dates = MAX(B.dates), id = MAX(B.id)--加一个id
FROM @tableC C
INNER JOIN @tableB B
ON C.bid = B.id
GROUP BY C.bookid
) T
ON A.bookid = T.bookid
INNER JOIN
(
SELECT B.dates, C.*
FROM @tableC C
INNER JOIN @tableB B
ON C.bid = B.id
) d
ON T.dates = d.dates
AND t.bookid = d.bookId
AND t.id = d.bid--关联时加上即可
/*
bookIDbookNameDatesValues
1name12013-07-04 00:00:00.0000.40
2name22013-07-04 00:00:00.0000.45
3name32013-07-04 00:00:00.0000.45
*/
