那里的标题不能很好地描述我从查询中需要什么。

@sgeddes帮助我提出了以下查询,但是该查询需要一些更改才能满足我的需求。我还对该查询进行了一些修改,以使其不选择“删除已删除的客户”,但是由于编写此查询的方式超出了我的SQL知识,因此我无法进行太多更改。

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 =945766  AND d.isActive > -1
        AND o2.customer_id IS NULL
GROUP BY d.customer_id


我需要以下三个要求。


为isActive> -1(-1 =已删除)的客户计算订单
客户不在订单表中(没有订单的客户)。
isActive = 0的客户及其相应的订单数


简而言之,我需要的是isActive = 0的customer并获得total_orders列的实际编号。

为了更好地帮助您理解我的要求,我创建了一个SqlFiddle。

Please see my SqlFiddle

最佳答案

如果您的要求是您在3点中提到的,则可以使用以下查询:

如果您要单独查询,请在下面使用-

SELECT d.customer_id, d.fname, COUNT(o.order_id) AS Total_Orders
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE d.isActive > -1
GROUP BY d.customer_id;

SELECT DISTINCT d.customer_id, d.fname
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE o.customer_id IS NULL;

SELECT d.customer_id, d.fname, COUNT(o.order_id) AS Total_Orders
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE d.isActive = 0
GROUP BY d.customer_id;


如果您想通过合并合并它们,请在下面使用-

SELECT 'isactive>-1' AS 'status', d.customer_id, d.fname, COUNT(o.order_id) AS Total_Orders
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE d.isActive > -1
GROUP BY d.customer_id
UNION ALL
SELECT DISTINCT 'Customer without Order' AS 'status', d.customer_id, d.fname, COUNT(o.order_id) AS Total_Orders
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE o.customer_id IS NULL
UNION ALL
SELECT 'isactive=0' AS 'status', d.customer_id, d.fname, COUNT(o.order_id) AS Total_Orders
FROM customer d
LEFT JOIN orders o ON d.customer_id=o.customer_id
WHERE d.isActive = 0
GROUP BY d.customer_id;


注意:您的主要查询正在尝试获取可以用于任何其他目的的最新订单,因此,如果您提供的确切要求与其他要求不同,那么有人可以为您提供帮助。

关于mysql - MySQL查询以选择客户的总订单,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36147778/

10-12 00:04
查看更多