时间戳

首页 > 解决方案 > MySQL：子查询未知列

mysql - MySQL：子查询未知列

问题描述

我的查询遇到了问题。我有一个包含订单、订单交易、订单产品和订单运费的数据库。现在我想要客户没有完全支付订单的所有订单。

我在这里创建了一个数据库小提琴。

通过此查询，我可以获得特定订单的订单总额，在本例中为订单 1：

SELECT SUM(total) AS orderTotal
FROM (
    SELECT order_id, SUM(price * amount) AS total
    FROM order_product
    WHERE order_id = '1'

    UNION ALL           
    SELECT order_id, price AS total
    FROM order_shipping
    WHERE order_id = '1'
) subtable
WHERE order_id <> 0 GROUP BY order_id

现在我想要支付金额（order_transaction）小于订单总额的所有订单。我尝试了以下查询，但它告诉我： Unknown column 'o.id' in 'where clause'. 没有行号，但它12在下面的查询中在线。

SELECT SQL_CALC_FOUND_ROWS o.id
FROM `order` o
WHERE (
        SELECT IFNULL(SUM(price),0) AS transactionTotal
        FROM order_transaction 
        WHERE order_id = o.id
      ) <=
      (
        SELECT SUM(total) AS orderTotal
        FROM (
            SELECT order_id, SUM(price * amount) AS total
            FROM order_product
            WHERE order_id = o.id

            UNION ALL           
            SELECT order_id, price AS total
            FROM order_shipping
            WHERE order_id = o.id
        ) subtable
        WHERE order_id <> 0 GROUP BY order_id
      ) 
ORDER BY o.id DESC
LIMIT 0, 10;

我不明白这不适用于第二个子查询，而它在第一个子查询（在 order_transaction 表上）中运行良好。

预期结果：

o.id 
------
 1
 2
 4
 8
 9
10

在我的数据库架构下方

CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_shipping` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_transaction` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

标签： mysql

解决方案

FROM 子句（您的 UNION 查询）中的子查询称为派生表。

在有关派生表的 MySQL 文档中，您会发现：

派生表不能是相关子查询。

这意味着您不能引用该子查询范围之外的表（别名），如下所示：

WHERE order_id = o.id

但是您可以在 SELECT 或 WHERE 子句中编写相关子查询。您的查询可以重写为：

SELECT SQL_CALC_FOUND_ROWS o.id
    , (
        SELECT IFNULL(SUM(price),0) AS transactionTotal
        FROM order_transaction 
        WHERE order_id = o.id
    ) a
    , (
        SELECT SUM(price * amount) AS total
        FROM order_product
        WHERE order_id = o.id
    ) b
    , (
        SELECT SUM(price) AS total
        FROM order_shipping
        WHERE order_id = o.id
    ) c
FROM `order` o
ORDER BY o.id DESC
LIMIT 0, 10;

HAVING a <= COALESCE(b, 0) + COALESCE(c, 0)

演示

它虽然不会返回您的预期结果。要解决这个问题，您可能需要更改<=为<.

推荐阅读