如何获得左联接中的前1条记录 [英] How to get Top 1 record in left join

问题描述

我的查询如下，我得到两个具有相同ordernumber的记录，我只需要左连接的最后一个前1个订单.

My query is as below , i am getting two records with same ordernumber , i need only last top 1 order with left join.

select DATEDIFF(HOUR,gso.Date1,goh.Date2),gso.Date1,goh.Date2,gso.OrderNumber from tableA gso left join tableB goh on gso.OrderId = goh.OrderId and goh.StatusId =278 and goh.ActionId =0
where gso.OrderStatusId not in(185,488,486,870)
and DATEDIFF(HOUR,gso.Date1,goh.Date2) <-1
gso.orderDate > '01/01/2011 00:00:00 AM'

假设orderid = 213537，则表B中有两个条目，如下所示
OrderId用户ID StatusId操作ID ChangedDate
213537 24249 278 0 2/26/2011 5:19:23 AM
213537 24249 278 0 2011/2/28下午01:07:19

我需要显示以上连接查询的最后一个条目.

suppose for orderid =213537, there is two entries in tableB as bellow
OrderId UserId StatusId ActionId ChangedDate
213537 24249 278 0 2/26/2011 5:19:23 AM
213537 24249 278 0 2/28/2011 1:07:19 PM

i need to display last entry with above join query

推荐答案

尝试一下，

try this,

select DISTINCT DATEDIFF(HOUR,gso.Date1,goh.Date2),gso.Date1,goh.Date2,gso.OrderNumber from tableA gso left join tableB goh on gso.OrderId = goh.OrderId and goh.StatusId =278 and goh.ActionId =0
where gso.OrderStatusId not in(185,488,486,870)
and DATEDIFF(HOUR,gso.Date1,goh.Date2) <-1
gso.orderDate > '01/01/2011 00:00:00 AM'

我以为您的代码没有错误，我只是添加了DISTINCT关键字，

希望对您有帮助，

如果有帮助，请标记为答案.

谢谢

i assumed that your code wass free from errors, i just added the DISTINCT keyword,

hope it helps,

mark as answer if it helps you..

thanks

select DISTINCT DATEDIFF(HOUR,gso.Date1,goh.Date2),gso.Date1,goh.Date2,gso.OrderNumber from tableA gso left join tableB goh on gso.OrderId = goh.OrderId and goh.StatusId =278 and goh.ActionId =0
and goh.Date2 = (select max(goh1.Date2) from tableB as goh1 where OrderId =goh.Orderid and goh1.StatusId =278)
where gso.OrderStatusId not in(185,488,486,870)
and DATEDIFF(HOUR,gso.Date1,goh.Date2) <-1
gso.orderDate > '01/01/2011 00:00:00 AM'

这篇关于如何获得左联接中的前1条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！