mysql - SQL 查询给出未知列的错误

Question

我正在用 PHP 创建功率计分析应用程序。我有以下表结构:

表:'提要'

| feed_id | device_no | current1 | voltage1 | power_factor_1 | vc1 | ic1 | date_added
-------------------------------------------------------------------------------------
| 36752   | 2         | 36.048   | 196.01   | 0.9            | 1   | 1   | 2014-06-23 14:14:44
| 36753   | 2         | 35.963   | 195.59   | 0.9            | 1   | 1   | 2014-06-23 14:15:34

等等。

表:'机器'

| machine_id | machine_phone | machine_name | company_id |
----------------------------------------------------------
| 1          | 2             | ABC Machine  | 1          |
| 2          | 093           | DEF Machine  | 1          |

我需要按小时记录，为此我编写了以下查询:

$sql =  "
            SELECT
                SUM(t.power1) AS 'power1'
                , HOUR(t.date) AS 'pulse_hour' 

            FROM    (
                SELECT
                    IF(@diff = 0, 0, (((f.voltage1*f.vc1)*(f.current1*f.ic1)*(f.power_factor_1))/1000) * (@diff/3600)) AS 'power1'
                    , IF(@diff = 0,0, TIME_TO_SEC(f.date_added) - @diff) AS 'deltaT'
                    , @diff := TIME_TO_SEC(f.date_added)
                    , f.date_added AS 'date'
                FROM
                    feeds f,
                    (SELECT @diff := 0) AS X
                left join
                    machine m
                on
                    f.device_no = m.machine_phone
                left join
                    company c
                on
                    c.company_id = m.company_id
            ";

    $sql .= $params['machine_id'] ? " where f.device_no = '".$params['machine_id']."'" : " where f.device_no > 0";
    $sql .= $params['machine_pulse_datetime_from'] ? " and f.date_added >= '".$params['machine_pulse_datetime_from']."'" : "";
    $sql .= $params['machine_pulse_datetime_to'] ? " and f.date_added <= '".$params['machine_pulse_datetime_to']."'" : "";
    $sql .= $params['company_id'] ? " and c.company_id = '".$params['company_id']."'" : "";

    $sql .= "
                ORDER BY
                    f.date_added ASC
                ) t
            GROUP BY HOUR(t.date)
            ORDER BY HOUR(t.date) ASC

            ";

如果我从查询中删除以下部分，查询运行正常:

left join
                    machine m
                on
                    f.device_no = m.machine_phone
                left join
                    company c
                on
                    c.company_id = m.company_id

但是对于这部分它给了我以下错误:

Error Code : 1054
Unknown column 'f.device_no' in 'on clause'

你能帮我解决这个问题吗...我花了一个小时来处理这个查询:(

Answer 1

这是您的加入:

         FROM feeds f,
              (SELECT @diff := 0) AS X left join
              machine m
              on f.device_no = m.machine_phone left join
              company c
              on c.company_id = m.company_id

问题是您混合了显式和隐式连接。您可以通过将逗号替换为 cross join 来解决此问题:

        FROM feeds f cross join
              (SELECT @diff := 0) AS X left join
              machine m
              on f.device_no = m.machine_phone left join
              company c
              on c.company_id = m.company_id

select 文档中隐藏的问题是 , 很像 cross join 除了异常(exception)范围规则——也就是说，当表别名被识别时。带逗号的表别名无法按您预期的方式识别。他们使用交叉连接。

Here是引用:

INNER [CROSS] JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER
JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.