mysql - 此类子句先前已解析

我有以下查询工作正常。

  SELECT d.customer_id,
          d.fname,
          d.lname,
          m.lastDate,
     (SELECT COUNT(order_id)
      FROM `orders`
      WHERE `customer_id`=d.customer_id
      ) AS 'total_orders',
          d.isActive
   FROM customers d
   JOIN `orders` m ON m.order_id=
     (SELECT order_id
      FROM `orders`
      WHERE customer_id=d.customer_id
      ORDER BY order_id DESC LIMIT 1
      )
   WHERE d.user_id=382
     AND d.customer_id NOT IN
       (SELECT `customer_id`
        FROM `orders`
        WHERE `balance`>0
          AND `isActive`=1
        )

上面的查询工作正常，但是当添加和联合查询还包括没有下任何订单的客户时，它确实可以工作。

   SELECT d.customer_id,
          d.fname,
          d.lname,
          m.lastDate,
     (SELECT COUNT(order_id)
      FROM `orders`
      WHERE `customer_id`=d.customer_id
      ) AS 'total_orders',
          d.isActive
   FROM customers d
   JOIN `orders` m ON m.order_id=
     (SELECT order_id
      FROM `orders`
      WHERE customer_id=d.customer_id
      ORDER BY order_id DESC LIMIT 1
      )
   WHERE d.user_id=382
     AND d.customer_id NOT IN
       (SELECT `customer_id`
        FROM `orders`
        WHERE `balance`>0
          AND `isActive`=1
        )

UNION

#customer WITH NO ORDERS
  SELECT `customer_id`,`fname`,`lname`,`state`,`city`,`isActive`
   FROM `customers`
   WHERE `user_id`=382
     AND `isActive` >-1
     AND `customer_id` NOT IN
       (SELECT `customer_id`
        FROM `orders`
        )

它在我的phpmyadmin中显示此错误

此类子句先前已解析（在select附近）

最佳答案

根据您的评论，我认为您不想使用union，而是要使用outer join。这是使用joins而不是所有correlated subqueries的查询的稍微简化的版本。

SELECT  d.customer_id, d.fname, d.lname, d.isactive,
        o.lastdate,
        Count(o2.order_id) AS 'total_orders'
 FROM   customers d
        LEFT JOIN (SELECT MAX(order_id) order_id, customer_id
              FROM   orders
              GROUP BY customer_id) m on d.customer_id = m.customer_id
        LEFT JOIN orders o on m.order_id = o.order_id
        LEFT JOIN orders o2 on d.customer_id = o2.customer_id
                           AND o2.balance > 0 AND o2.isactive = 1
 WHERE  d.user_id = 382
        AND o2.customer_id IS NULL
 GROUP BY d.customer_id

顺便说一句-使用union语句查看编辑内容时，每个select列表中的列数和类型必须相同。第二个查询中的state和city字段可能与第一个查询中的lastDate和count字段具有不同的数据类型。

关于mysql - 此类子句先前已解析，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36051226/