MySQL嵌套，嵌套子查询无法获取外部变量

Question

我有一个支出表和一个日期表，它们由 date_id 和 id 连接...

我想做的是从1个查询中获取所有支出信息，加上所有支出的总和，但有限制和/或抵消

这是现在的查询

SELECT spendings.id, spendings.price, spendings.title, 
       dates.date, users.username, currencies.value,
       ( SELECT SUM(sum_table.price) 
         FROM (
             SELECT s.price
             FROM spendings s, dates d
             WHERE s.date_id = d.id 
               AND day(d.date) = 25
             LIMIT 2 OFFSET 0
         ) as sum_table
       ) AS sum_price
FROM spendings, dates, users, currencies
WHERE spendings.date_id = dates.id 
  AND day(dates.date) = 25 
  AND spendings.user_id = users.id 
  AND spendings.curr_id = currencies.id
LIMIT 2 OFFSET 0

输出

id  price   title   date       username value  sum_price
3   6.00    title1  2013-11-25 alex     €      21.00
4   15.00   title2  2013-11-25 alex     €      21.00

它有效，但前提是此处的日期 day(d.date) = 25 与此处的外部日期 day(dates.date) = 25

如果我输入 day(d.date) = day(dates.date) 这似乎是符合逻辑的事情，我会得到 #1054 - Unknown column 'dates.date'在“where 子句”中

如果有人有想法让这个更简单，请告诉我:)

Answer 1

尝试加入而不是使用嵌套相关子查询:

SELECT spendings.id, spendings.price, spendings.title, 
      dates.date, users.username, currencies.value,
      y.sum_price
FROM spendings, dates, users, currencies
JOIN (
    SELECT day, SUM(sum_table.price) As sum_price
    FROM (
        SELECT day(d.date) As day,
               s.price
        FROM spendings s, dates d
        WHERE s.date_id = d.id 
          AND day(d.date) = 25
        LIMIT 2 OFFSET 0
    ) sum_table
    GROUP BY day
) y
ON y.day = day(dates.date)
WHERE spendings.date_id = dates.id 
  -- AND day(dates.date) = 25 <== commented since it's redundant now
  AND spendings.user_id = users.id 
  AND spendings.curr_id = currencies.id

<小时/>

一些评论:

<小时/>

不建议使用带有逗号的旧连接语法:FROM table1,table2,table2 WHERE
表达连接的推荐方法是“新的”ANSI SQL 连接语法:

FROM table1
[left|right|cross|[full] outer|natural] JOIN table2 {ON|USING} join_condition1
[left|right|cross|[full] outer|natural] JOIN table3 {ON|USING} join_condition2
....

实际上这个“新语法”现在已经很老了，因为我记得它是在 1992 年 - 22 年前发布的。在IT行业，22年就像22岁。

<小时/>