时间戳

首页 > 解决方案 > SQL Server - 查找下过两种类型订单的所有客户

sql - SQL Server - 查找下过两种类型订单的所有客户

问题描述

我正在尝试从客户那里提取所有未结订单的列表，其中同一客户同时使用了我们的一种特殊付款类型以及我们的标准选项之一。具体来说，那些有预付款或 10n30 的未结订单和至少一个正常订单的订单。因此，在下面的示例表中，我想返回order_id1、3 和 4。

cust_orders                       order_info
+----------+-----------+          +----------+-------------+----------+
| cust_id  | order_id  |          | order_id |  pay_type   |  status  |
+----------+-----------+          +----------+-------------+----------+
|       1  |         1 |          |       1  | standard    | open     |
|       1  |         2 |          |       2  | prepay      | closed   |
|       1  |         3 |          |       3  | prepay      | open     |
|       1  |         4 |          |       4  | 10n30       | open     |
|       2  |         5 |          |       5  | standard    | deferred |
|       2  |         6 |          |       6  | prepay      | open     |
|       3  |         7 |          |       7  | N/A         | deferred |
|       4  |         8 |          |       8  | prepay      | open     |
|       4  |         9 |          |       9  | standard    | closed   |
|       4  |        10 |          |      10  | prepay      | open     |
+----------+-----------+          +----------+-------------+----------+

我有以下查询

SELECT *
FROM cust_orders AS co
    LEFT JOIN ( SELECT *
              FROM order_info
              WHERE pay_type IN('prepay', '10n30')
              AND status = 'open' ) AS o1 on o1.order_id = co.order_id
    LEFT JOIN ( SELECT *
              FROM order_info
              WHERE pay_type NOT IN('prepay', '10n30')
              AND status = 'open' ) AS o2 on o2.order_id = co.order_id
WHERE o1.order_id IS NOT NULL
     AND o2.order_id IS NOT NULL
ORDER BY co.order_id DESC;

但它运行非常缓慢并返回一堆重复项。

我查看了搜索具有两种产品的订单，一种具有特定参考，另一种具有特定描述，并选择所有具有多个项目的订单并检查所有项目状态，但似乎都不是我需要的。

编辑：感谢 gjvdkamp 提供以下代码的基础；我修改了他们的解决方案以在更大的查询中使用，现在一切正常。

SELECT co.*, [other fields]
FROM cust_order AS co
     LEFT JOIN [other tables]
WHERE cust_id IN ( SELECT co.cust_id
                   FROM cust_order AS co
                        LEFT JOIN order_info o on o.order_id = co.order_id
                   WHERE o.status = 'open'
                   GROUP BY co.cust_id
                   HAVING SUM(CASE WHEN o.pay_type IN ('prepay', '10n30') THEN 1 ELSE 0 END) > 0 
                      AND SUM(CASE WHEN (o.pay_type NOT IN ('prepay', '10n30') OR o.pay_type IS NULL) THEN 1 ELSE 0 END) > 0)

标签： sqlsql-server

解决方案

'handrolled pivot' 在这里可以很好地工作：

select cust_id,
       sum(case when pay_type = 'normal'             then 1 else 0 end) as NormalCount,
       sum(case when pay_type in ('prepay', '10n30') then 1 else 0 end) as OtherCount
from   cust_order co 
       inner join order o on co.order_id = o.order_id 
where  o.status = 'open'
       and o.pay_type in ('normal','prepay','10n30')
group by cust_id
having NormalCount> 0 and 
       OtherCount > 0

这只需要一个连接（如果你有正确的索引，则合并）然后聚合它。不知道您的订单表上的统计信息，但在 pay_type 上添加了 where 语句以获得良好的衡量标准。这将很难在速度方面击败..

编辑：删除了 with 语句，因为它甚至不需要

推荐阅读