Oracle/SQL -多表连接逻辑-不确定如何表达它
我想要做的是找到所有在前一天购买了Series X或Series A部件的客户，只要这是他们第一次从那里购买部件。如果他们购买了多个符合条件的小部件，Series X会获得排名，而我应该只会得到它的记录。希望这是有意义的，所以这是数据
[customers]
c_id cp_id
--------------
1 cp1
2 cp2
3 cp3
4 cp4
5 cp5
6 cp6
7 cp7
8 cp8
9 cp9
10 cp10
[widget_orders - c_id maps to c_id in customers]
c_id w_sku o_date
----------------------
1 w1 2012-10-10
2 w1 2012-10-10
2 w2 2012-10-10
3 w1 2012-10-10
3 w2 2012-10-10
4 w1 2012-10-10
4 w2 2012-10-10
5 w1 2012-10-10
5 w2 2012-10-10
6 w1 2012-10-10
6 w2 2012-10-10
7 w2 2012-10-10
8 w1 2012-10-10
9 w3 2012-10-10
[widgets - w_sku maps to w_sku in widget_orders]
w_sku w_series
------------------
w1 Series A
w2 Series X
w3 Series C
[customer_data - c_id maps to c_id in customers]
cp_id seriesA_fPurch seriesX_fPurch
--------------------------------------
cp3
cp4 2012-09-15
cp5 2012-09-15
cp6 2012-09-15 2012-09-15
cp7 2012-09-15
cp8 2012-09-15这里是我想要返回的数据，忽略()中的描述
cp_id series
--------------
cp1 Series A (bought series A and had NO prior purchase history)
cp2 Series X (bought both series, but X has rank - no purchase history)
cp3 Series X (bought both series, but X has rank - has purchase history recors albeit not for these)
cp4 Series X (bought both series, but X has rank - already had A history anyways)
cp5 Series A (bought both series, although X has rank they had previously bought series X)以下人员未显示在结果中
cp6 - they had previously bought both series
cp7 - bought a series x, but had in the past
cp8 - bought a series a, but had in the past
cp9 - bought a widget in neither series
cp10 - didnt buy anything希望这一切都有意义，有人能帮我解决这个问题！
因此，为了总结逻辑并在这里更清晰地定义它，我将如何以一种类似步骤的方式描述需要发生的事情
1) Find all customers who have no matching records in the customer_data table
2) Find all customers who have a null value in either *purch column in the customer_data table
3) Combine these results together
4) Take the results and find the customers who made a purchase yesterday
5) Take the results and find the customers who purchased Series A or Series X
6) Take the results and do the following
6a) If the purchase was Series A and they have a value for series A purch already drop them from results
6b) If the purchase was Series X and they have a value for series X purch already drop them from results
7) Take the results and remove duplicate records based on the cp_id - Series X takes presedence over Series A回答 1
Stack Overflow用户
发布于 2012-10-12 05:27:09
我不确定我是否完全理解您的要求，但请尝试：
SELECT cp_id, w_series
FROM (
SELECT rank() over (partition BY wo."c_id" ORDER BY decode(w."w_series",'Series X',1,'Series A',2)) rank,
wo."c_id" c_id,
c."cp_id" cp_id,
w."w_series" w_series
FROM widget_orders wo JOIN widgets w ON wo."w_sku"=w."w_sku"
JOIN customers c on c."c_id"=wo."c_id"
LEFT OUTER JOIN customer_data cd ON c."cp_id" = cd."cp_id"
WHERE w."w_series" IN ('Series A', 'Series X')
AND trunc(wo."o_date") = trunc(sysdate)-1
AND ( (cd."seriesA_fPurch" IS NULL AND w."w_series"='Series A')
OR (cd."seriesX_fPurch" IS NULL AND w."w_series"='Series X'))
)
WHERE rank = 1Here是一把小提琴
解释：
根据阶梯状的数字-
1) + 2)是在LEFT OUTER JOIN + cd."seriesA_fPurch" IS NULL条件+ cd."seriesX_fPurch" IS NULL条件下完成的，因为它还会找到那些没有记录的人，并将空值放入其中。
3)显而易见的……
4) trunc(wo."o_date") = trunc(sysdate)-1条件
5) w."w_series" IN ('Series A', 'Series X')
6) (cd."seriesA_fPurch" IS NULL AND w."w_series"='Series A') OR (cd."seriesX_fPurch" IS NULL AND w."w_series"='Series X')条件
7)通过对记录和WHERE rank = 1条件进行排序
https://stackoverflow.com/questions/12848159
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号