时间戳

首页 > 解决方案 > 如何简化 Oracle DB 的查询？

sql - 如何简化 Oracle DB 的查询？

问题描述

OrderA
-------
userId
orderDate
amount

OrderB
------
userId
orderDate

sample data:

OrderA
1  01/01/2018 100
1  02/01/2018 101
1  03/01/2018 102

OrderB
1  01/01/2018 
1  01/10/2018 
1  03/01/2018

我有两个订单表，OrderA 和 OrderB。我想编写一个查询，对 OrderA 订单的用户订单金额求和，但如果同一用户在同一个月放置 OrderB，则仅包括 OrderA 条目。

对于此示例数据，查询应给出202.

此查询有效，但distinct速度很慢。

SELECT userId, SUM (a.amount) 
FROM (
            SELECT DISTINCT a.userId,
                            a.amount
            FROM            OrderA a
            INNER JOIN      OrderB b  
            ON              EXTRACT(month from a.orderDate) = EXTRACT(month from b.orderDate) AND
                            EXTRACT(year from a.orderDate) = EXTRACT(year from b.orderDate) AND
                            a.userId = b.userId
    )
GROUP BY userId

此查询不起作用，它给出301

SELECT          userId, SUM (a.amount) 
FROM            OrderA a
INNER JOIN      OrderB b  
ON              EXTRACT(month from a.orderDate) = EXTRACT(month from b.orderDate) AND
                EXTRACT(year from a.orderDate) = EXTRACT(year from b.orderDate) AND
                a.userId = b.userId    
GROUP BY userId

有什么建议么？我觉得有更好的方法来实现这个查询。

标签： sqloracle

解决方案

这是做你想做的吗？

SELECT a.userId, SUM(a.amount) 
FROM OrderA a
WHERE EXISTS (SELECT 1
              FROM OrderB b  
              WHERE TO_CHAR(a.orderDate, 'YYYY-MM') = TO_CHAR(b.orderDate, 'YYYY-MM') AND
                    a.userId = b.userId   
            ) 
GROUP BY a.userId;

出于性能考虑，您需要在OrderB(userId, orderDate).

推荐阅读