查询：从第一个表中选择详细信息以及最新的第二个表详细信息 [英] query: Select details from first table along with latest second table details

问题描述

我有两张桌子：

tblCust（id，name，phone）

数据：

id |名称|手机

1 | abc | 99565665

2 | def | 98654571

3 | ghi | 96554574



和

tblOrder（OrderNo，id，oSDate，oEndDate）

数据：

OrderNo | id | oSDate | oEndDate

o1 | 1 | jan1,2012 | mar1,2012

o2 | 1 | jan2,2012 | mar3,2012

o3 | 2 | feb1,2012 | apr3,2012

o3 | 2 | feb2,2012 | apr4,2012

o3 | 2 | feb3,2012 | apr4,2012

o3 | 2 | feb4,2012 | apr3,2012



现在，我想获得最新订单详细信息以及每个客户的customerDetails

解决方案

我想你问了一些关于加入的事情。

请参考以下链接然后你会得到一些关于这个的信息



http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ [ ^ ]



http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html [<一个href =http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.htmltarg et =_ blanktitle =New Window> ^ ]



http://beginner-sql-tutorial.com/sql-joins.htm [ ^ ]



http://www.techonthenet.com/sql/joins.php [ ^ ]



请明确参考这个链接你有一些想法。

下面的查询将有效..

;  WITH  cte  as  
（
  SELECT  id，max（oSDate） AS  latestStartDate 
  FROM  tblOrder 
  group   BY  id 
）
 
  SELECT  C。*，O。* 
  FROM  tblCust  AS  C 
  LEFT   JOIN  tblOrder  AS  O  on  O.id = C.id 
  LEFT   JOIN  cte  AS  Temp   Temp.id = C.id 
  WHERE  O.oSDate = Temp.latestStartDate 

I have two tables:
tblCust(id, name, phone)
Data:
id| name| phone
1 | abc | 99565665
2 | def | 98654571
3 | ghi | 96554574

and
tblOrder(OrderNo,id,oSDate,oEndDate)
Data:
OrderNo | id | oSDate | oEndDate
o1 | 1 | jan1,2012 | mar1,2012
o2 | 1 | jan2,2012 | mar3,2012
o3 | 2 | feb1,2012 | apr3,2012
o3 | 2 | feb2,2012 | apr4,2012
o3 | 2 | feb3,2012 | apr4,2012
o3 | 2 | feb4,2012 | apr3,2012

Now, i want to get the latest orderDetails along with customerDetails for each customer

解决方案

Hi, i think Your asking something about joins.
Please refer the below links then you get some information regarding this

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html[^]

http://beginner-sql-tutorial.com/sql-joins.htm[^]

http://www.techonthenet.com/sql/joins.php[^]

please refer this links definately you got some idea .

Below query will work..

;WITH cte as
(
SELECT id,max(oSDate) AS latestStartDate
FROM tblOrder
group BY id
)

SELECT C.*,O.*
FROM tblCust AS C
LEFT JOIN  tblOrder  AS O on  O.id =C.id
LEFT JOIN cte AS Temp on Temp.id = C.id
WHERE O.oSDate = Temp.latestStartDate

这篇关于查询：从第一个表中选择详细信息以及最新的第二个表详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！