mysql - 将变量传递给子查询

Question

我想从每个项目中提取所有客户的报价，如果他们有多个报价，我想要平均值。

我有以下查询:

SELECT
@projectid := projects.id AS projectid,
(SELECT
  SUM(`offers`) FROM
  (SELECT AVG( `price` ) AS `offers`
    FROM `sales`
    WHERE `sales`.`projectid` = @projectid
    AND `sales`.`active` = 'yes'
    GROUP BY `sales`.`clientid`
  ) AS `average`
) AS `outstanding`
FROM projects
WHERE
projects.active = 'yes'
ORDER BY outstanding ASC 

我的问题是@projectid 没有传递到子查询中，我不明白应该如何解决这个问题。

谁能给我一些建议吗？

Answer 1

请记住，SQL 是一种声明式语言，而不是命令式语言。使用 JOIN 可以解决很多问题和没有“变量”的子查询。所以……

...我不太确定理解 - 但您是否只需要“每个项目对每个客户的平均报价求和”，这样的事情就可以了:

SELECT S.projectid, SUM(offers) FROM
  (
    SELECT projectid, clientid, AVG(price) as offers FROM sales WHERE active = 'yes'
    GROUP BY projectid, clientid
  ) AS S 
JOIN projects on S.projectid = projects.id
AND projects.active = "yes"
GROUP BY projectid

参见 http://sqlfiddle.com/#!2/19d0f/24