MySQL:平均 OF 子查询(不在 FROM/GROUP BY 中)

Question

我想显示我数据库中产品组的平均价格。因此，我想返回一段时间内该产品组内所有产品的平均价格。

首先，我想知道该组的价格变化了多少。此查询成功返回所有日期，此查询...

SELECT DISTINCT(DATE(gbp.prdate)) as dte
FROM sku AS s
  INNER JOIN mt_price gbp ON s.id = gbp.
WHERE (gbp.prdate < NOW() - INTERVAL ? DAY) AND (s.grp = ?)`

然后我在 php 中使用 foreach 生成以下查询...

$i = 1;
$qry = "SELECT s.id AS sid, s.sku, s.title AS s_title,";
foreach ($dates as $date) {
  $qry .= "(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE(?) ORDER BY prdate LIMIT 1) AS ?, ";
  $datearr[] = $date;
}
$qry = substr($qry, 0, -2) . " ";
$qry .= "FROM sku AS s
  WHERE (s.grp = ?)
  GROUP BY s.id
  ORDER BY s.id";
$results = $db->prepare($qry);
foreach($datearr as $darr) {
  $results->bindParam($i++,$darr->dte,PDO::PARAM_STR);
  $results->bindParam($i++,$darr->dte,PDO::PARAM_STR);
}

上面的一个例子给了我这个查询...

SELECT s.id AS sid, s.rid, s.sku, s.title AS s_title,
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-08') ORDER BY prdate LIMIT 1) AS '2015-01-08',
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-12') ORDER BY prdate LIMIT 1) AS '2015-02-12',
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-26') ORDER BY prdate LIMIT 1) AS '2015-01-26',
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-08') ORDER BY prdate LIMIT 1) AS '2015-02-08',
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-30') ORDER BY prdate LIMIT 1) AS '2015-01-30',
  (SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-02') ORDER BY prdate LIMIT 1) AS '2015-02-02'

从 sku AS s WHERE (s.cat = 'TLPT01') 按 s.id 分组 按 s.id 排序

当我按上面的方式运行此查询时，我得到了我所期望的结果，每个 SKU 仅一个价格(低于 LIMIT)并且是正确的价格。然后它会随着 DATE 的变化正确显示 SKU 的价格。对我好!

所以，我想要的是获得所有这些结果的平均值。如果我在 s.id 前面使用 AVG 我会得到 SKU 的平均值，这显然不是一个有意义的结果。我想要返回的所有 SKU 的平均价格(因此，GROUP BY 应该只给我一行信息)。当我将 GROUP BY 放在子查询之前时，我只是得到一个错误。

Answer 1

尝试如下进行子查询 (SELECT AVG(price) FROM price WHERE price.sid = s.id AND prdate <= DATE(?) GROUP BY price.sid)