子查询内的主体子查询

问题描述：

我想转换原始mysql查询以使用主义。该表中充满了统计行，我的查询正在检查以查看距离平均值数据增益偏离平均每天增长的距离。子查询内的主体子查询

SQL版本完全按照我期望的方式工作。转换为教义给了我一个错误。

以下是原文：

SELECT 
    l.*, 
    DAY(l.created_at) as day, 
    MONTH(l.created_at) as month, 
    YEAR(l.created_at) as year, 
    (
     MAX(l.infamyrenown) - 
     MIN(l.infamyrenown) - 
     (
      SELECT AVG(infamydifference) as avginf FROM 
      (
        SELECT (
         MAX(inf.infamyrenown) - 
         MIN(inf.infamyrenown) 
         ) as infamydifference 
        FROM lotro_record inf 
        GROUP BY DAY(inf.created_at) 
      ) as p1 
     ) 
    ) as infamy_deviance 
FROM 
    lotro_record l 
GROUP BY 
    year,month,day

而这里的碎主义查询：

Doctrine_Core::getTable("LotroRecord") 
->createQuery("l") 
->select("l.*") 
->addSelect("DAY(created_at)") 
->addSelect("MONTH(created_at)") 
->addSelect("YEAR(created_at)") 
->addSelect("(
    MAX(l.infamyrenown) - 
    MIN(l.infamyrenown) - 
    (
     select AVG(infamydifference) as avginf FROM (
      SELECT (
       MAX(inf.infamyrenown) - 
       MIN(inf.infamyrenown) 
      ) as infamydifference 
      FROM LotroRecord inf 
      GROUP BY DAY(inf.created_at) 
     ) as p1 
    ) 
) as infamy_deviance") 
->where("lotro_character_id = {$this->getId()}") 
->groupBy("DAY(created_at)");

产生这个SQL：

SELECT l.id          AS l__id, 
     l.infamyrenown        AS l__infamyrenown, 
     l.kills          AS l__kills, 
     l.killing_blows        AS l__killing_blows, 
     l.kills_above_rating      AS l__kills_above_rating, 
     l.kills_below_rating      AS l__kills_below_rating, 
     l.deaths         AS l__deaths, 
     l.lotro_character_id      AS l__lotro_character_id, 
     l.created_at        AS l__created_at, 
     l.updated_at        AS l__updated_at, 
     DAY(l.created_at)       AS l__0, 
     MONTH(l.created_at)       AS l__1, 
     YEAR(l.created_at)       AS l__2, 
     (Max(l.infamyrenown) - Min(l.infamyrenown) - (SELECT 
     Avg(infamydifference) AS avginf 
                 FROM 
      (SELECT (Max(l2.infamyrenown) - Min(l2.infamyrenown)) AS l__0 
      FROM lotro_record l2 
      GROUP BY DAY(l2.created_at)) AS p1)) AS l__3 
FROM lotro_record l 
WHERE (l.lotro_character_id = 1) 
GROUP BY DAY(l.created_at)

的错误是：

未知柱'字段列表'中的'infamydifference'

任何想法？

答

我认为这要你在你写的DQL查询称之为inf.infamydifference，而不是仅仅infamydifference：

select AVG(inf.infamydifference) as avginf

子查询内的主体子查询

相关推荐