MySQL Group By with Sum 删除了太多信息

Question

谁能帮我理解 MySQL 的 Group By 是如何工作的以及我做错了什么？我正在使用一个包含两个表、拍卖表和批次的数据库。批次表有一列“auctionid”，它将每个批次链接到相关拍卖，拍卖和批次之间具有一对多关系。

我从拍品和拍卖中选择大量信息，包括使用几个 sum 和 count(distinct) 函数在每次拍卖的基础上对拍品表中的一些记录进行总计。

我的查询是这样的：

SELECT
  auction.`countryId`,
  auction.`date`,
  auction.`name`,
  auction.`awardComments`,
  lots.`lotName`,
  lots.`region`,
  lots.`popCovered`,

  SUM(lots.'size'),
  SUM(lots.`paired`),
  SUM(lots.`unpaired`),
  COUNT(DISTINCT 'lots.winner')

FROM auction join lots USING ('awardid') 

GROUP BY lots.'awardid'

“awardid”是拍卖表的主键和批次表的外键。 “lotid”是lots 表的主键，但未使用。

当我运行此查询时，每次拍卖都会得到一行，我正在寻找的是每个批次的一行，该拍卖中每个批次的拍卖信息相同，并且总和和计数函数计算总大小和数量拍品表中每次拍卖的获胜者。如果我去掉查询的“group by”和“sum”部分，那么我会得到正确的行数，每批一行。有没有办法通过 sum 和 count 函数获取我需要的所有行？基本上，我只希望将 group 子句应用于 SUM 和 COUNT 函数，如果这有意义的话 - 我希望为每次拍卖计算获胜者的数量，但显示在该拍卖中每批的结果中。

Answer 1

SELECT
  a.countryId,
  a.`date`,
  a.name,
  a.awardComments,
  l.lotName,
  l.region,
  l.popCovered,

  sums.total_size,
  sums.total_paired,
  sums.total_unpaired,
  sums.winners

FROM auction a
INNER JOIN lots l on (a.awardid = l.awardid) 
INNER JOIN (
  SELECT 
    a2.id as auction_id
    SUM(l2.size) as total_size,
    SUM(l2.paired) as total_paired,
    SUM(l2.unpaired) as total_unpaired,
    COUNT(DISTINCT l2.winner) as winners
  FROM AUCTION a2 INNER JOIN lots l2 ON (a2.awardid = l2.awardid)
  GROUP BY a2.id
  ) sums ON (sums.auction_id = a.id) 

对于来自同一拍卖的拍品，这确实会在每次拍卖中重复相同的总数。

Answer 2

我想这样的东西可以工作（警告：未经测试！）：

SELECT 
  auction.`countryId`,
  auction.`date`,
  auction.`name`,
  auction.`awardComments`,
  lots.`lotName`,
  lots.`region`,
  lots.`popCovered`,
  sums.`size`,
  sums.`paired`,
  sums.`unpaired`,
  sums.`winners`
FROM auction
JOIN lots USING (`awardid`)
JOIN (
  SELECT
    `awardid`,
    SUM(`size`) AS `size`,
    SUM(`paired`) AS `paired`,
    SUM(`unpaired`) AS `unpaired`,
    COUNT(DISTINCT lots.`winner`) AS `winners`
  FROM lots GROUP BY `awardid`
) AS sums USING (`awardid`)

也就是说，您在 lots 表上进行子选择来计算每次拍卖的总和，然后将其结果视为一个单独的表，您可以将其连接到结果中。