此类子句先前已被解析

一尘不染

此类子句先前已被解析

sql

我有以下查询工作正常。

  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附近）

阅读 151

2021-05-16

共1个答案

一尘不染

根据您的评论，我认为您不想使用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领域，从第一。

2021-05-16