时间戳

首页 > 解决方案 > 合并两个表的最有效方法，包括第一个表的所有数据和第二个表的缺失数据

sql - 合并两个表的最有效方法，包括第一个表的所有数据和第二个表的缺失数据

问题描述

我有两个表，一个包含用户选择的策略，另一个包含所有策略：

-- Policies selected by client
create table client_policies (
  id number,
  client_id number,
  policy_id varchar2(30),
  policy_description varchar2(100),
  policy_added_date timestamp,
  policy_modified_date timestamp
)
/

-- All available policies
create table policies (
  policy_id varchar2(30),
  policy_description varchar2(100)
)
/

示例数据：

insert into client_policies values (1, 123, 'HOUSE_INSURANCE', 'house insurance policy', sysdate - 10, sysdate - 3);
insert into client_policies values (2, 123, 'HEALTH_INSURANCE', 'health insurance policy', sysdate - 20, sysdate - 5);
insert into client_policies values (3, 123, 'CIVIL_LIABILITY_INSURANCE', 'civil liability insurance policy', sysdate - 2, sysdate - 1);

insert into policies values ('HOUSE_INSURANCE', 'house insurance policy');
insert into policies values ('HEALTH_INSURANCE', 'health insurance policy');
insert into policies values ('CIVIL_LIABILITY_INSURANCE', 'civil liability insurance policy');
insert into policies values ('CAR_INSURANCE', 'car insurance policy');
insert into policies values ('ETC_INSURANCE', 'etc... insurance policy');

有两种情况：

我有一个可以产生所需输出的光标，但是它缺乏性能。我的问题是如何改进，或者是否有另一个更好的解决方案来解决这个问题？

declare
  cursor c_get_client_policies(CP_CLIENT_ID            in number,
                               CP_GET_DEFAULT_POLICIES in varchar2) is
    with policies_data as
     (select 1 rank,
             id,
             policy_id,
             policy_description,
             policy_added_date,
             policy_modified_date
        from client_policies cp
       where client_Id = CP_CLIENT_ID
      union
      select 2                  rank,
             null               id,
             policy_id,
             policy_description,
             null               policy_added_date,
             null               policy_modified_date
        from policies p
       where CP_GET_DEFAULT_POLICIES = 'Y'),
    ranked as
     (select row_number() over(partition by policy_id order by rank) row_no,
             pd.*
        from policies_data pd)
    select id,
           policy_id,
           policy_description,
           policy_added_date,
           policy_modified_date
      from ranked
     where row_no = 1
     order by id;
begin
  for r in c_get_client_policies(123, 'Y') loop
    dbms_output.put_line('ID=' || r.id || ', POLICY_ID=' || r.POLICY_ID ||
                         ', POLICY_DESCRIPTION=' || r.policy_description ||
                         ', POLICY_ADDED_DATE=' || r.POLICY_ADDED_DATE ||
                         ', POLICY_MODIFIED_DATE=' ||
                         r.POLICY_MODIFIED_DATE);
  end loop;
end;
/

预期输出：

ID POLICY_ID POLICY_DESCRIPTION POLICY_ADDED_DATE POLICY_MODIFIED_DATE
1 HOUSE_INSURANCE 房屋保险单 2021.06.11 18:27:02,000000 2021.06.18 18:27:02,000000
2 健康保险 健康保险政策 2021.06.01 18:27:02,000000 2021.06.16 18:27:02,000000
3 CIVIL_LIABILITY_INSURANCE 民事责任保险单 2021.06.19 18:27:02,000000
汽车保险 汽车保险单
ETC_INSURANCE 等等……保险单

标签： sqloracle

解决方案

一个直接的左连接似乎完全符合您的描述？

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=032e5e5f0a1856636f2ba144fd9eb390

SELECT
  client_policies.id,
  policies.policy_id,
  COALESCE(client_policies.policy_description, policies.policy_description)  AS policy_description,
  client_policies.policy_added_date,
  client_policies.policy_modified_date  
FROM
  policies
LEFT JOIN
  client_policies
    ON  client_policies.policy_id = policies.policy_id
    AND client_policies.client_Id = CP_CLIENT_ID
WHERE
     client_policies.policy_id IS NOT NULL
  OR CP_GET_DEFAULT_POLICIES = 'Y'

推荐阅读