sql - 如何删除输出中的重复记录

Question

我有这个查询：

select o.customers_customer_id, oi.product_product_id, oi.quantity, o.order_date
from orders o
join order_item oi on oi.ORDERS_ORDER_ID=o.ORDER_ID
where o.ORDER_DATE ='12/31/2015'; 

它返回 8 条记录，但是，一个客户 ID 出现两次。

输出：

EH-14185    OFF-PA-10002421 4   12/31/2015
JP-16135    OFF-ST-10001526 4   12/31/2015
JP-16135    OFF-BI-10002194 1   12/31/2015
KD-16495    OFF-BI-10004654 6   12/31/2015
BM-11785    OFF-LA-10003720 2   12/31/2015
BD-11605    FUR-CH-10000454 1   12/31/2015
CJ-11875    OFF-AR-10004269 5   12/31/2015
BM-11785    OFF-SU-10001664 2   12/31/2015

distinct不工作

该查询基于 2 个表：orders和order_item

订单表

Name                  Null     Type         
--------------------- -------- ------------ 
ORDER_ID              NOT NULL VARCHAR2(30) 
ORDER_DATE                     VARCHAR2(30) 
CUSTOMERS_CUSTOMER_ID NOT NULL VARCHAR2(30) 

order_item 表

Name                 Null     Type         
-------------------- -------- ------------ 
QUANTITY                      VARCHAR2(30) 
LINE_ITEM_SALE_PRICE          VARCHAR2(19) 
ORDERS_ORDER_ID      NOT NULL VARCHAR2(30) 
PRODUCT_PRODUCT_ID   NOT NULL VARCHAR2(30) 

我在customers_customer_id 列上尝试了不同的

select o.customers_customer_id, oi.product_product_id, oi.quantity, o.order_date
from orders o
join order_item oi on oi.ORDERS_ORDER_ID=o.ORDER_ID
where o.ORDER_DATE ='12/31/2015' ; 

EH-14185    OFF-PA-10002421 4   12/31/2015
JP-16135    OFF-ST-10001526 4   12/31/2015
KD-16495    OFF-BI-10004654 6   12/31/2015
BM-11785    OFF-LA-10003720 2   12/31/2015
BD-11605    FUR-CH-10000454 1   12/31/2015
CJ-11875    OFF-AR-10004269 5   12/31/2015
BM-11785    OFF-SU-10001664 2   12/31/2015

标签： sqloracleoracle12c

Answer 1

这取决于您想对这些“重复项”做什么。如果您不关心其余值，一种选择是聚合它们，例如

select o.customers_customer_id, 
       max(oi.product_product_id) product_id, 
       max(oi.quantity) quantity, 
       max(o.order_date) order_date
from orders o
join order_item oi on oi.ORDERS_ORDER_ID=o.ORDER_ID
where o.ORDER_DATE ='12/31/2015'
group by c.customers_customer_id;