SELECT a.ID, a.UID,a.FSupplyID, a.FBillNo, a.FItemIDName, a.FCancellation, a.FDate, b.DisplayName, c.FMemo, c.FDate AS FDDate, a.FSendDate
FROM dbo.TB_Order a LEFT JOIN
dbo.TB_User b ON a.FSupplyID = b.SID LEFT JOIN
dbo.TB_Feedback c ON a.FItemIDName = c.FItemIDName AND
a.FBillNo = c.FBillNo AND c.FDate=
(SELECT MAX(FDATE)
FROM TB_Feedback
WHERE a.FItemIDName = c.FItemIDName AND a.FBillNo = c.FBillNo)
我实际的语句是这样的,但是取到的纪录中除了c里面纪录日期最新的一条,其他c.FMemo,FDDate都是null,
而不是根据a.FItemIDName = c.FItemIDName AND a.FBillNo = c.FBillNo分组后,每组的日期最新的纪录
http://topic.csdn.net/u/20080518/23/38430880-5a60-464c-9bc3-2e16b56bcfee.html
FROM dbo.TB_Order a LEFT JOIN
dbo.TB_User b ON a.FSupplyID = b.SID LEFT JOIN
dbo.TB_Feedback c ON a.FItemIDName = c.FItemIDName AND
a.FBillNo = c.FBillNo AND c.FDate=
(SELECT MAX(FDATE)
FROM TB_Feedback
WHERE a.FItemIDName = c.FItemIDName AND a.FBillNo = c.FBillNo)
我实际的语句是这样的,但是取到的纪录中除了c里面纪录日期最新的一条,其他c.FMemo,FDDate都是null,
而不是根据a.FItemIDName = c.FItemIDName AND a.FBillNo = c.FBillNo分组后,每组的日期最新的纪录
http://topic.csdn.net/u/20080518/23/38430880-5a60-464c-9bc3-2e16b56bcfee.html
--try:SELECT a.ID, a.UID,a.FSupplyID, a.FBillNo, a.FItemIDName, a.FCancellation, a.FDate, b.DisplayName, c.FMemo, c.FDate AS FDDate, a.FSendDate
FROM dbo.TB_Order a LEFT JOIN
dbo.TB_User b ON a.FSupplyID = b.SID
LEFT JOIN
(select * from TB_Feedback t where not exists(select 1 from TB_Feedback where FItemIDName = t.FItemIDName AND FBillNo = t.FBillNo and FDate>t.FDate))c
ON a.FItemIDName = c.FItemIDName AND
a.FBillNo = c.FBillNo
that's just what i need