PL / SQL-Oracle 9.1
|
这是我们正在此处处理的方案。我们有一个客户表和一个销售表。这些表通过客户表中的事务ID联接。
顾客可以从商店购买任何水果。
例如，我们需要做的是找出：多少顾客在购买樱桃之前就购买了苹果。
Table structure:
Cust - Cust ID, Transaction ID, ...
Sales - Transaction ID, Fruit ID, Insert date for record, ...
没有找到相关结果
已邀请:
2 个回复
俯乡骚钵皆
select apple.cust_id from ( select c.cust_id, min(s.sale_date) as sale_date from cust c join sales s on s.transaction_id = c.transaction_id where s.fruit_id = \'CHERRY\' group by c.cust_id ) cherry , ( select c.cust_id, min(s.sale_date) as sale_date from cust c join sales s on s.transaction_id = c.transaction_id where s.fruit_id = \'APPLE\' group by c.cust_id ) apple where cherry.cust_id = apple.cust_id and cherry.sale_date > apple.sale_date /select cust_id from ( select c.cust_id , min(case when s.fruit_id = \'CHERRY\' = s.sale_date else null end) as cherry_date , min(case when s.fruit_id = \'APPLE\' = s.sale_date else null end) as apple_date from cust c join sales s on s.transaction_id = c.transaction_id group by c.cust_id ) cherry where cherry_date > apple_date /结乳
select cust_id, min(case when fruit_id = \'Apple\' then sale_date end) first_apple_purchase, min(case when fruit_id = \'Cherry\' then sale_date end) first_cherry_purchase from transactions group by cust_id having min(case when fruit_id = \'Apple\' then sale_date end) < min(case when fruit_id = \'Cherry\' then sale_date end)