使用LEFT JOIN进行Mysql查询并从另一个表中获取OrderID的数量...坚持

问题描述：

This is my current query and it's working fine as I expect it.

SELECT a.ID, a.CreatedDate, a.Status, b.OrderTotal, 
    e.Rate, f.RouteType, f.Code, g.Country, h.Network 
    from orders AS a
     LEFT JOIN order_details AS b ON a.ID = b.OrderID
     LEFT JOIN order_routes AS d ON a.ID = d.OrderID
     LEFT JOIN userrate AS e ON e.ID = d.RouteID
     LEFT JOIN ratelist AS f ON f.ID = e.RateListID
     LEFT JOIN country AS g ON f.CountryID = g.ID
     LEFT JOIN network AS h ON f.NetworkID = h.ID
     WHERE a.UserID = 16 ORDER BY a.CreatedDate DESC

However now I am trying to add another column which shows me count of appearances of each OrderID from order_routes

My order routes look like this right now

ID   OrderID   RouterID
1      1         1
2      1         2
3      2         2
4      2         3
5      2         5

So I want column which shows me how many appearances OrderID have in order_routes table.

I think I need sub query inside my query but when I try that it giving me error. To let you know

a.ID = d.OrderID

e.ID = d.RouteID

Please help.

这是我当前的查询，它正如我预期的那样正常工作。 p>

  SELECT a.ID，a.CreatedDate，a.Status，b.OrderTotal，
 e.Rate，f.RouteType，f.Code，g.Country，h.Network 
 from orders AS a 
  LEFT JOIN order_details AS b ON a.ID = b.OrderID 
 LEFT JOIN order_routes AS d ON a.ID = d.OrderID 
 LEFT JOIN userrate AS e ON e.ID = d.RouteID 
 LEFT JOIN ratelist AS f  ON f.ID = e.RateListID 
 LEFT JOIN country AS g ON f.CountryID = g.ID 
 LEFT JOIN network AS h ON f.NetworkID = h.ID 
 WHERE a.UserID = 16 ORDER BY a。  CreatedDate DESC 
  code>  pre> 
 
 但是现在我想添加另一个列，显示order_routes中每个OrderID的出现次数 p> 
 
 
 我的订单路线现在看起来像这样 p> 
 
 
  ID订单ID RouterID 
1 1 1 
2 1 2 
3 2 2 
4 2 3 
5 2 5 
  code  >  pre> 
 
 所以我想要列wh  ich告诉我OrderID在order_routes表中有多少次出现。 p> 
 
 
我想在我的查询中需要子查询但是当我尝试它给我错误时。 让你知道 p> 
 
 
 a.ID = d.OrderID  p> 
 
 
 e.ID = d.RouteID  p> 
 
 
请帮忙。 p> 
  div>

答

try this

SELECT a.ID, a.CreatedDate, a.Status, b.OrderTotal, 
    e.Rate, f.RouteType, f.Code, g.Country, h.Network, d2.cnt AS OrderCount 
FROM orders AS a
    LEFT JOIN order_details AS b ON a.ID = b.OrderID
    LEFT JOIN order_routes AS d ON a.ID = d.OrderID
    LEFT JOIN (SELECT OrderID as OrderID2, COUNT(*) AS cnt FROM order_routes GROUP BY 1) AS d2 ON a.ID = d2.OrderID2
    LEFT JOIN userrate AS e ON e.ID = d.RouteID
    LEFT JOIN ratelist AS f ON f.ID = e.RateListID
    LEFT JOIN country AS g ON f.CountryID = g.ID
    LEFT JOIN network AS h ON f.NetworkID = h.ID
    WHERE a.UserID = 16 ORDER BY a.CreatedDate DESC

使用LEFT JOIN进行Mysql查询并从另一个表中获取OrderID的数量...坚持

相关推荐