sql server 多表查询

/*订单表和商品表根据商品ID(CommodityInfoId）关联*/
SELECT t1.CommodityInfoId,t1.OrderID,t1.OrderTime,t1.BuyNumber,t1.TotalPrice,t2.CommodityName,t2.EndTime FROM dbo.Orders t1 LEFT join dbo.CommodityInfo t2 ON t2.CommodityInfoId = t1.CommodityInfoId
/*商品表和商品图片表是一对多的关系，首先根据商品ID进行分组(GROUP BY),取得图片表中的ID,商品ID*/
SELECT MAX(Id) AS ID,CommodityInfoId FROM dbo.CommodityImg GROUP BY CommodityInfoId
/*取得商品表分组后的ID*/
SELECT tab.ID from (SELECT MAX(Id) AS ID,CommodityInfoId FROM dbo.CommodityImg GROUP BY CommodityInfoId) AS tab
/*将第一张表的查询结果集与第三张合并*/
SELECT tab3.* , tab2.ImgPath FROM (SELECT t1.CommodityInfoId,t1.OrderID,t1.OrderTime,t1.BuyNumber,t1.TotalPrice,t2.CommodityName,t2.EndTime FROM dbo.Orders t1 LEFT join dbo.CommodityInfo t2 ON t2.CommodityInfoId = t1.CommodityInfoId )AS tab3 LEFT JOIN (SELECT ImgPath,CommodityInfoId FROM dbo.CommodityImg WHERE ID IN(SELECT tab.ID from (SELECT MAX(Id) AS ID,CommodityInfoId FROM dbo.CommodityImg GROUP BY CommodityInfoId) AS tab)) AS tab2 ON tab2.CommodityInfoId = tab3.CommodityInfoId ORDER BY tab3.OrderTime DESC