mysql - 在 SQL 查询的 JOIN 中计数 (*)

Question

我有以下报告查询，一切工作正常，但我需要在报告中添加一个变量，该变量根据“manheim_auction_listings”记录中的记录数总计返回的每条记录的记录数。我觉得它需要在一个连接内，但在任何地方我都会使用“COUNT(*) AS num_of_runs”，它似乎使整个查询只返回一行，其中包含查询中的记录总数，而不是所有带有变量 num_of_runs 的行，其中包含每个 CAR 记录的“manheim_auction_listings”记录数。

SELECT products.client_id,
    clients.name AS client_name,
    manheim_auction_lanes.lane_number,
    manheim_auction_listings.sequence,
    manheim_auction_listings.gross_sale_price,
    products.asking_price,  products.asking_price_condition, 
    manheim_auctions.auction_date,
    manheim_auctions.auction_number,
    product_purchases.total_spent,
    product_purchases.purchase_price 
FROM manheim_auction_listings
    JOIN cars ON 
    cars.id = manheim_auction_listings.car_id 
    JOIN products ON 
     cars.product_id = products.id 
    JOIN product_purchases ON
     current_product_purchase_id = product_purchases.id
    JOIN manheim_auctions ON 
     manheim_auctions.id = manheim_auction_listings.manheim_auction_id 
    JOIN manheim_auction_lanes ON 
      manheim_auction_lanes.id = manheim_auction_listings.manheim_auction_lane_id 
    JOIN clients ON 
     clients.id = products.client_id 
     AND clients.id LIKE $P{LoggedInUserAttribute_ClientID} 
WHERE 
     manheim_auctions.auction_number = $P{SaleNumber} 
     AND manheim_auctions.`year` = $P{SaleYear} 
ORDER BY manheim_auction_lanes.lane_number DESC,
    manheim_auction_listings.sequence DESC

Answer 1

请尝试以下操作...

SELECT products.client_id,
       clients.name AS client_name,
       manheim_auction_lanes.lane_number,
       manheim_auction_listings.sequence,
       manheim_auction_listings.gross_sale_price,
       num_of_runs,
       products.asking_price,  products.asking_price_condition,
       manheim_auctions.auction_date,
       manheim_auctions.auction_number,
       product_purchases.total_spent,
       product_purchases.purchase_price 
FROM ( SELECT manheim_auction_id AS manheim_auction_id,
              COUNT( manheim_auction_id ) AS num_of_runs
       FROM manheim_auction_listings
       GROUP BY manheim_auction_id
     ) AS num_of_runs_finder
JOIN manheim_auction_listings ON manheim_auction_listings.manheim_auction_id = num_of_runs.manheim_auction_id
JOIN cars ON cars.id = manheim_auction_listings.car_id
JOIN products ON cars.product_id = products.id
JOIN product_purchases ON current_product_purchase_id = product_purchases.id
JOIN manheim_auctions ON manheim_auctions.id = manheim_auction_listings.manheim_auction_id
JOIN manheim_auction_lanes ON manheim_auction_lanes.id = manheim_auction_listings.manheim_auction_lane_id
JOIN clients ON clients.id = products.client_id 
            AND clients.id LIKE $P{LoggedInUserAttribute_ClientID}
WHERE manheim_auctions.auction_number = $P{SaleNumber}
  AND manheim_auctions.`year` = $P{SaleYear} 
ORDER BY manheim_auction_lanes.lane_number DESC,
         manheim_auction_listings.sequence DESC

这是通过将其他表连接到一个计算与每个 manheim_auction_id 关联的列表数量的表来实现的。 ，有效地附加 manheim_auction_id计算 manheim_auction_id 所在的每一行发生。

如果num_of_runs是根据其他一些标准计算的，那么请相应地向我提出建议。

如果您有任何问题或意见，请随时发表评论。