将表连接到自身

Question

这是我正在用的桌子

EnquiryId     Day  CheckIn    Start  StartName   Destination  StayAt StayAtName
RH-0123201412  1  2014-12-25    99    Cochin     Trivandrum     3   Munnar
RH-0123201412  2  2014-12-26    3     Munnar     Trivandrum     3   Munnar
RH-0123201412  3  2014-12-27    3     Munnar     Trivandrum     8   Kanyakumari

现在我需要的结果如下：

EnquiryId     Day  CheckIn   Start  ComingFrom   GoinTo      StayAt StayAtName
RH-0123201412  1  2014-12-25  99      Cochin     KanyaKumari    3   Munnar
RH-0123201412  3  2014-12-27  3       Munnar     Trivandrum     8   Kanyakumari

我正在尝试下面的代码，但它并不完全正确

SELECT q.Enquiry_Id
    ,q.Start
    ,q1.Stay_At
    ,q.Ckeck_In
    ,q1.Stay_At_Name
    ,q.Start_Name
FROM Quick_Plan q
    ,Quick_Plan q1
WHERE q.Enquiry_Id = 'RH-0123201412'
    AND q1.Enquiry_Id = 'RH-0123201412'
    AND q.Stay_At = q1.Start

从这个查询中，我得到了以下结果

EnquiryId    Start Stay_At CheckIn      StayAtName     Startname
RH-0123201412   99  3     2014-12-25    Munnar        Cochin Airport
RH-0123201412   99  8     2014-12-25    Kanyakumari   Cochin Airport
RH-0123201412   3   3     2014-12-26    Munnar        Munnar
RH-0123201412   3   8     2014-12-26    Kanyakumari   Munnar

有没有人能建议我怎么做才能得到我想要的结果。提前谢谢。

Answer 1

不确定这是不是你要找的东西？如果不是，你想要发生什么？

SELECT q.Enquiry_Id
,q.Start
,Max(q.Stay_At) Stay_At
,Max(q.Ckeck_In) Check_in
,Max(q.Stay_At_Name) Stay_At_Name
,q.Start_Name
FROM Quick_Plan q
WHERE q.Enquiry_Id = 'RH-0123201412'
group by Enquiry_Id, Start, Startname