sql-部分依赖于另一个表选择行

gev0vcfq 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(337)

我对数据库和查询非常陌生。我不确定这是不是很基本。请帮我找到解决办法。
我有两个表-subscription和customer，主键分别为subscription\u id和customer\u id。customer\u id是指向customer表customer\u id列的外键。下表及其数据示例：
订阅

subscription_id  customer_id   subscription_start_date   subscription_end_date  subscription_status 
1001             1             1-JAN-2020                31-DEC-2020              PENDING       
1002             2             1-JUN-2020                31-MAY-2021              PENDING       
1003             3             4-JUL-2020                3-JUL-2021               ARCHIVED  
1004             4             2-APR-2020                1-APR-2021               PENDING           
1005             5             3-APR-2020                2-APR-2021               ARCHIVED      
1006             6             21-JAN-2020               20-JAN-2021              PENDING   
1007             7             22-JAN-2020               21-JAN-2021              PENDING

顾客

customer_id membership_type  membership_start_date   membership_status 
1            GOLD             1-JAN-2020             ACTIVE
2            PLATINUM         1-JUN-2020             ACTIVE
3            PLATINUM         5-JUL-2020             PROCESSING
4            GOLD             2-APR-2020             PROCESSING
5            GOLD             3-APR-2020             ACTIVE
6            GOLD             21-JAN-2020            PROCESSING
7            GOLD             22-JAN-2019            EXPIRED

我想查询满足以下两个条件的所有订阅
订阅状态处于挂起状态，会员类型为黄金或白金，会员状态为活动状态。（10011002）
只有当客户成员资格类型为白金且成员资格开始日期介于订阅开始日期和订阅结束日期之间且成员资格状态为处理时，订阅状态才被存档（1003）
所以我们应该在标准1下得到10011002条记录。标准2下为1003

sql Database oracle oracle-sqldeveloper oracle11g

来源：https://stackoverflow.com/questions/62229716/sql-select-rows-partially-depending-on-another-table

1条答案

按热度按时间

9bfwbjaz1#

查询应该是这样的；行#1-21表示示例数据，您不能键入该数据。您确实需要的查询从第22行开始，包含 WHERE 第一个满足第一个条件，第二个满足第二个条件。
注意样本 MEMBERSHIP_START_DATE 对于游标 3 是 02-JUL-2020 不是在订阅开始之间( 4-JUL-2020 )结束( 3-JUL-2021 )日期，所以- 1003 不是结果集的一部分。修改会员开始日期，例如。 22-JUL-2020 会的。

SQL> with
  2  subscription (subscription_id, customer_id, subscription_start_date,
  3    subscription_end_date, subscription_status) as
  4    (select 1001, 1, date '2020-01-01', date '2020-12-31', 'PENDING'  from dual union all
  5     select 1002, 2, date '2020-06-01', date '2021-05-31', 'PENDING'  from dual union all
  6     select 1003, 3, date '2020-07-04', date '2021-07-03', 'ARCHIVED' from dual union all
  7     select 1004, 4, date '2020-04-02', date '2021-04-01', 'PENDING'  from dual union all
  8     select 1005, 5, date '2020-04-03', date '2021-04-02', 'ARCHIVED' from dual union all
  9     select 1006, 6, date '2020-01-21', date '2021-01-20', 'PENDING'  from dual union all
 10     select 1007, 7, date '2020-01-22', date '2021-01-21', 'PENDING'  from dual
 11    ),
 12  customer (customer_id, membership_type, membership_start_date,
 13    membership_status) as
 14    (select 1, 'GOLD'    , date '2020-01-01', 'ACTIVE'     from dual union all
 15     select 2, 'PLATINUM', date '2020-06-01', 'ACTIVE'     from dual union all
 16     select 3, 'PLATINUM', date '2020-07-02', 'PROCESSING' from dual union all
 17     select 4, 'GOLD'    , date '2020-04-02', 'PROCESSING' from dual union all
 18     select 5, 'GOLD'    , date '2020-04-03', 'ACTIVE'     from dual union all
 19     select 6, 'GOLD'    , date '2020-01-21', 'PROCESSING' from dual union all
 20     select 7, 'GOLD'    , date '2019-01-22', 'EXPIRED'    from dual
 21    )

22  select s.subscription_id
 23  from subscription s join customer c on c.customer_id = s.customer_id
 24  where (    s.subscription_status = 'PENDING'
 25         and c.membership_type in ('GOLD', 'PLATINUM')
 26         and c.membership_status = 'ACTIVE'
 27        )
 28     or
 29       (     s.subscription_status = 'ARCHIVED'
 30         and c.membership_type = 'PLATINUM'
 31         and c.membership_start_date between s.subscription_start_date
 32                                         and s.subscription_end_date
 33         and c.membership_status = 'PROCESSING'
 34       )
 35  /

SUBSCRIPTION_ID
---------------
           1001
           1002

SQL>

赞(0）回复(0）举报 2021-07-26

我来回答

sql-部分依赖于另一个表选择行

1条答案

相关问题

热门标签

最新问答