文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >需要在sql中识别匹配记录集的帮助

问需要在sql中识别匹配记录集的帮助
EN

Stack Overflow用户
提问于 2015-03-02 16:36:25
回答 1查看 50关注 0票数 1

我正在做一些已经设置的业务的重新映射，需要在Oracle 11的SQL中执行以下操作。

这是一个“一次性的活动”(不是交付给我的客户)，所以我计划不使用编程语言来实现它。我有下面的表格。下面是用于设置的DDL和DML。

代码语言：javascript
复制
subsriber_cd | prod_id | rate plan | charge_ID | QoS_1  | QoS_2  | QoS_3  | RatePerUnit |
-------------+---------+-----------+-----------+--------+--------+--------+-------------| 
pete         | 1       | RP1       | 100       | Credit | Analog |        | 0.1         |
pete         | 1       | RP1       | 100       | Credit | GSM    |        | 0.2         |
pete         | 1       | RP1       | 100       | Credit | LTE    |        | 0.3         |
pete         | 1       | RP1       | 100       | Debit  | Analog |        | 1.1         |
pete         | 1       | RP1       | 100       | Debit  | GSM    |        | 1.2         |
pete         | 1       | RP1       | 100       | Debit  | LTE    |        | 1.3         |
pete         | 1       | RP1       | 101       | Credit | Analog |        | 2.1         |
pete         | 1       | RP1       | 101       | Credit | GSM    |        | 2.2         |
pete         | 1       | RP1       | 101       | Credit | LTE    |        | 2.3         |
pete         | 1       | RP1       | 101       | Debit  | Analog |        | 3.1         |
pete         | 1       | RP1       | 101       | Debit  | GSM    |        | 3.2         |
pete         | 1       | RP1       | 101       | Debit  | LTE    |        | 3.3         |

chris        | 2       | RP2       | 100       | Credit | Analog |        | 5.1         |
chris        | 2       | RP2       | 100       | Credit | GSM    |        | 5.2         |
chris        | 2       | RP2       | 100       | Credit | LTE    |        | 5.3         |
chris        | 2       | RP2       | 100       | Debit  | Analog |        | 6.1         |
chris        | 2       | RP2       | 100       | Debit  | GSM    |        | 6.2         |
chris        | 2       | RP2       | 100       | Debit  | LTE    |        | 6.3         |
chris        | 2       | RP2       | 101       | Credit | Analog |        | 7.1         |
chris        | 2       | RP2       | 101       | Credit | GSM    |        | 7.2         |
chris        | 2       | RP2       | 101       | Credit | LTE    |        | 7.3         |
chris        | 2       | RP2       | 101       | Debit  | Analog |        | 8.1         |
chris        | 2       | RP2       | 101       | Debit  | GSM    |        | 8.2         |
chris        | 2       | RP2       | 101       | Debit  | LTE    |        | 8.3         |

jack         | 3       | RP3       | 100       | Credit | Analog |        | 0.1         |
jack         | 3       | RP3       | 100       | Credit | GSM    |        | 0.2         |
jack         | 3       | RP3       | 100       | Credit | LTE    |        | 0.3         |
jack         | 3       | RP3       | 100       | Debit  | Analog |        | 1.1         |
jack         | 3       | RP3       | 100       | Debit  | GSM    |        | 1.2         |
jack         | 3       | RP3       | 100       | Debit  | LTE    |        | 1.3         |
jack         | 3       | RP3       | 101       | Credit | Analog |        | 2.1         |
jack         | 3       | RP3       | 101       | Credit | GSM    |        | 2.2         |
jack         | 3       | RP3       | 101       | Credit | LTE    |        | 2.3         |
jack         | 3       | RP3       | 101       | Debit  | Analog |        | 3.1         |
jack         | 3       | RP3       | 101       | Debit  | GSM    |        | 3.2         |
jack         | 3       | RP3       | 101       | Debit  | LTE    |        | 3.3         |

...                                                                                      

dave         | 4       | RP4       | 100       | Credit | Analog |        | 10.1        |
dave         | 4       | RP4       | 100       | Credit | GSM    |        | 10.2        |
dave         | 4       | RP4       | 100       | Credit | LTE    |        | 10.3        |
dave         | 4       | RP4       | 100       | Debit  | Analog |        | 10.1        |
dave         | 4       | RP4       | 100       | Debit  | GSM    |        | 10.2        |
dave         | 4       | RP4       | 100       | Debit  | LTE    |        | 10.3        |
dave         | 4       | RP4       | 101       | Credit | Analog |        | 10.1        |
dave         | 4       | RP4       | 101       | Credit | GSM    |        | 10.2        |
dave         | 4       | RP4       | 101       | Credit | LTE    |        | 10.3        |
dave         | 4       | RP4       | 101       | Debit  | Analog |        | 10.1        |
dave         | 4       | RP4       | 101       | Debit  | GSM    |        | 10.2        |
dave         | 4       | RP4       | 101       | Debit  | LTE    |        | 10.3        |

每个订户都被分配了一个独特的产品，这是设计上的一个错误。这导致现在我的系统中有数千种产品。

作为重新设计，我计划不为每个用户创建单独的产品，而是创建一个产品和费率计划，并添加一个新的QoS_3，以帮助我提供不同的RatePerUnits。

因为皮特和杰克有相同的RatePerUnit，所以我计划为他们两个存储相同的QoS_3。因此，我需要找到具有匹配RatePerUnit的记录集，以便可以将它们合并为一个QoS_3。

在上表中，我可以合并皮特和杰克的QoS_3，因为它们对其他字段的所有组合都有相同的RatePerUnit，从而形成了我的表。

代码语言：javascript
复制
subsriber_cd | prod_id | rate plan | charge_ID | QoS_1  | QoS_2  | QoS_3  | RatePerUnit |
-------------+---------+-----------+-----------+--------+--------+--------+-------------| 
pete         | 5       | RP5       | 100       | Credit | Analog | Q_1    | 0.1         |
pete         | 5       | RP5       | 100       | Credit | GSM    | Q_1    | 0.2         |
pete         | 5       | RP5       | 100       | Credit | LTE    | Q_1    | 0.3         |
pete         | 5       | RP5       | 100       | Debit  | Analog | Q_1    | 1.1         |
pete         | 5       | RP5       | 100       | Debit  | GSM    | Q_1    | 1.2         |
pete         | 5       | RP5       | 100       | Debit  | LTE    | Q_1    | 1.3         |
pete         | 5       | RP5       | 101       | Credit | Analog | Q_1    | 2.1         |
pete         | 5       | RP5       | 101       | Credit | GSM    | Q_1    | 2.2         |
pete         | 5       | RP5       | 101       | Credit | LTE    | Q_1    | 2.3         |
pete         | 5       | RP5       | 101       | Debit  | Analog | Q_1    | 3.1         |
pete         | 5       | RP5       | 101       | Debit  | GSM    | Q_1    | 3.2         |
pete         | 5       | RP5       | 101       | Debit  | LTE    | Q_1    | 3.3         |

chris        | 5       | RP5       | 100       | Credit | Analog | Q_2    | 5.1         |
chris        | 5       | RP5       | 100       | Credit | GSM    | Q_2    | 5.2         |
chris        | 5       | RP5       | 100       | Credit | LTE    | Q_2    | 5.3         |
chris        | 5       | RP5       | 100       | Debit  | Analog | Q_2    | 6.1         |
chris        | 5       | RP5       | 100       | Debit  | GSM    | Q_2    | 6.2         |
chris        | 5       | RP5       | 100       | Debit  | LTE    | Q_2    | 6.3         |
chris        | 5       | RP5       | 101       | Credit | Analog | Q_2    | 7.1         |
chris        | 5       | RP5       | 101       | Credit | GSM    | Q_2    | 7.2         |
chris        | 5       | RP5       | 101       | Credit | LTE    | Q_2    | 7.3         |
chris        | 5       | RP5       | 101       | Debit  | Analog | Q_2    | 8.1         |
chris        | 5       | RP5       | 101       | Debit  | GSM    | Q_2    | 8.2         |
chris        | 5       | RP5       | 101       | Debit  | LTE    | Q_2    | 8.3         |

jack         | 5       | RP5       | 100       | Credit | Analog | Q_1    | 0.1         |
jack         | 5       | RP5       | 100       | Credit | GSM    | Q_1    | 0.2         |
jack         | 5       | RP5       | 100       | Credit | LTE    | Q_1    | 0.3         |
jack         | 5       | RP5       | 100       | Debit  | Analog | Q_1    | 1.1         |
jack         | 5       | RP5       | 100       | Debit  | GSM    | Q_1    | 1.2         |
jack         | 5       | RP5       | 100       | Debit  | LTE    | Q_1    | 1.3         |
jack         | 5       | RP5       | 101       | Credit | Analog | Q_1    | 2.1         |
jack         | 5       | RP5       | 101       | Credit | GSM    | Q_1    | 2.2         |
jack         | 5       | RP5       | 101       | Credit | LTE    | Q_1    | 2.3         |
jack         | 5       | RP5       | 101       | Debit  | Analog | Q_1    | 3.1         |
jack         | 5       | RP5       | 101       | Debit  | GSM    | Q_1    | 3.2         |
jack         | 5       | RP5       | 101       | Debit  | LTE    | Q_1    | 3.3         |

...                                                                                  

dave         | 5       | RP5       | 100       | Credit | Analog | Q_4    | 10.1        |
dave         | 5       | RP5       | 100       | Credit | GSM    | Q_4    | 10.2        |
dave         | 5       | RP5       | 100       | Credit | LTE    | Q_4    | 10.3        |
dave         | 5       | RP5       | 100       | Debit  | Analog | Q_4    | 10.1        |
dave         | 5       | RP5       | 100       | Debit  | GSM    | Q_4    | 10.2        |
dave         | 5       | RP5       | 100       | Debit  | LTE    | Q_4    | 10.3        |
dave         | 5       | RP5       | 101       | Credit | Analog | Q_4    | 10.1        |
dave         | 5       | RP5       | 101       | Credit | GSM    | Q_4    | 10.2        |
dave         | 5       | RP5       | 101       | Credit | LTE    | Q_4    | 10.3        |
dave         | 5       | RP5       | 101       | Debit  | Analog | Q_4    | 10.1        |
dave         | 5       | RP5       | 101       | Debit  | GSM    | Q_4    | 10.2        |
dave         | 5       | RP5       | 101       | Debit  | LTE    | Q_4    | 10.3        |

您可以看到当用户现在不必为pete、chris、jack和dave输入12*4 RatePerUnit，而是输入12*3 RatePerUnit时的好处。

这将与我的重新设计产生巨大的不同，因为我所有的订阅者都有大约40K条记录。

DDL和DMLs

代码语言：javascript
复制
CREATE TABLE combination (
    subsriber_cd VARCHAR2(20),
    prod_id NUMBER(5),
    rate_plan VARCHAR2(20),
    charge_id NUMBER(5),
    qos_1  VARCHAR2(20),
    qos_2  VARCHAR2(20),
    qos_3  VARCHAR2(20),
    rateperunit NUMBER(10,2)
);

INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Credit', 'Analog', '', 0.1);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Credit', 'GSM', '', 0.2);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Credit', 'LTE', '', 0.3);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Debit', 'Analog', '', 1.1);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Debit', 'GSM', '', 1.2);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 100, 'Debit', 'LTE', '', 1.3);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Credit', 'Analog', '', 2.1);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Credit', 'GSM', '', 2.2);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Credit', 'LTE', '', 2.3);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Debit', 'Analog', '', 3.1);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Debit', 'GSM', '', 3.2);
INSERT INTO combination
     VALUES ('pete', 1, 'RP1', 101, 'Debit', 'LTE', '', 3.3);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Credit', 'Analog', '', 5.1);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Credit', 'GSM', '', 5.2);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Credit', 'LTE', '', 5.3);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Debit', 'Analog', '', 6.1);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Debit', 'GSM', '', 6.2);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 100, 'Debit', 'LTE', '', 6.3);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Credit', 'Analog', '', 7.1);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Credit', 'GSM', '', 7.2);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Credit', 'LTE', '', 7.3);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Debit', 'Analog', '', 8.1);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Debit', 'GSM', '', 8.2);
INSERT INTO combination
     VALUES ('chris', 2, 'RP2', 101, 'Debit', 'LTE', '', 8.3);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Credit', 'Analog', '', 0.1);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Credit', 'GSM', '', 0.2);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Credit', 'LTE', '', 0.3);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Debit', 'Analog', '', 1.1);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Debit', 'GSM', '', 1.2);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 100, 'Debit', 'LTE', '', 1.3);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Credit', 'Analog', '', 2.1);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Credit', 'GSM', '', 2.2);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Credit', 'LTE', '', 2.3);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Debit', 'Analog', '', 3.1);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Debit', 'GSM', '', 3.2);
INSERT INTO combination
     VALUES ('jack', 3, 'RP3', 101, 'Debit', 'LTE', '', 3.3);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Credit', 'Analog', '', 10.1);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Credit', 'GSM', '', 10.2);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Credit', 'LTE', '', 10.3);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Debit', 'Analog', '', 10.1);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Debit', 'GSM', '', 10.2);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 100, 'Debit', 'LTE', '', 10.3);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 101, 'Credit', 'Analog', '', 10.1);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 101, 'Credit', 'GSM', '', 10.2);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 101, 'Credit', 'LTE', '', 10.3);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 101, 'Debit', 'Analog', '', 10.1);
INSERT INTO combination
     VALUES ('dave', 4, 'RP4', 101, 'Debit', 'GSM', '', 10.2);
INSERT INTO combination
matching
recordset
sql
oracle
EN

回答 1

Stack Overflow用户

发布于 2015-03-02 21:11:35

如果我理解正确的话，您希望基于相同的rateperunit更新QOS_3。此合并将完成此操作：

代码语言：javascript
复制
merge into combination c1
using (
  select row_number() over (order by rpu) rn, rpu
    from (
      select rateperunit rpu
        from combination
        group by rateperunit) 
  ) c2
on (c1.rateperunit = c2.rpu)
when matched then update set c1.qos_3 = 'Q_'||lpad(c2.rn, 4, '0')

结果：

代码语言：javascript
复制
select * from combination order by qos_3

pete   1   RP1   100     Credit   Analog   Q_0001   0.1
jack   3   RP3   100     Credit   Analog   Q_0001   0.1
pete   1   RP1   100     Credit   GSM      Q_0002   0.2
jack   3   RP3   100     Credit   GSM      Q_0002   0.2
pete   1   RP1   100     Credit   LTE      Q_0003   0.3
jack   3   RP3   100     Credit   LTE      Q_0003   0.3
...
dave   4   RP4   101   Credit     GSM      Q_0026  10.2
dave   4   RP4   101   Credit     LTE      Q_0027  10.3
dave   4   RP4   100   Credit     LTE      Q_0027  10.3
dave   4   RP4   100   Debit      LTE      Q_0027  10.3
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/28805506

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档