通过评估其他值[保留]来编写计算列-Java 学习之路

-3

我有如下的示例记录：

客户表

我想通过检查其他记录的值来编写 Renewal . 我的意思是， New Business 列将检查所有数据并查看 Customer_id=110 和 Customer_name=John 是否会放 'Renewal'

我怎样才能做到这一点？

3 回答

据我所知，如果 NewBusiness 具有空值并且与给定的某些值匹配，则应该用 Renewal 值替换 . 因此，请以下列方式使用 exists 关键字：

with tab(CustomerID, CustomerName, ProductID, NewBusiness ) as
(
  select 110, 'John', 110, 'New' from dual union all
  select 100, 'John', 100,  null from dual    
) 
 select CustomerID as "Customer ID", 
        CustomerName as "Customer Name", 
        ProductID as "Product ID",
        nvl(NewBusiness, 
             ( select 'Renewal'
                 from dual
                where exists ( select 1 
                                 from tab 
                                where CustomerID = 110
                                  and CustomerName = 'John' )))
                                  as "New Business"
   from tab;

Customer ID Customer Name  Product ID   New Business
----------- -------------  ----------- -------------
     110         John           110          New
     100         John           100        Renewal

Rextester Demo

回复于 2024-04-28T02:41:14+08:00

这里's how I understood the question: there'是一个包含一些行的表，并且您希望将其 NEW_BUSINESS 列更新为"New"或"Renewal" . 从您提供的测试用例（相当差）和缺少导致 NEW_BUSSINESS 值的规则 - 似乎您将 PRODUCT_ID 的最大 PRODUCT_ID 设置为"New"，而其他的是"Renewals"，这里有一个显示如何做的选项那 .

我创建了一个更丰富的测试用例：

SQL> create table customer
  2    (customer_id number,
  3     customer_name varchar2(10),
  4     product_id number,
  5     new_business varchar2(10));

Table created.

SQL> insert into customer (customer_id, customer_name, product_id)
  2    select 110, 'John', 110 from dual union all
  3    select 110, 'John', 100 from dual union all
  4    select 110, 'John', 150 from dual union all
  5    select 200, 'Mike', 200 from dual union all
  6    select 300, 'Nina', 300 from dual union all
  7    select 300, 'Nina', 310 from dual;

6 rows created.

更新和结果：

SQL> update customer c set
  2    c.new_business = (select case when t.rn = 1 then 'New'
  3                                  else 'Renewal'
  4                             end
  5                      from (select u.customer_id,
  6                                   u.product_id,
  7                              row_number() over (partition by u.customer_id
  8                                                 order by u.product_id desc) rn
  9                            from customer u
 10                           ) t
 11                      where t.customer_id = c.customer_id
 12                        and t.product_id = c.product_id
 13                     );

6 rows updated.

SQL> select * From customer order by customer_id, product_id desc;

CUSTOMER_ID CUSTOMER_N PRODUCT_ID NEW_BUSINE
----------- ---------- ---------- ----------
        110 John              150 New
        110 John              110 Renewal
        110 John              100 Renewal
        200 Mike              200 New
        300 Nina              310 New
        300 Nina              300 Renewal

6 rows selected.

SQL>

回复于 2024-04-28T02:41:14+08:00

编写一个过程以将数据插入表中 . 过程中的代码应执行将数据正确插入表中的所有步骤 . 例如：

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER(pinCUSTOMER_ID   IN NUMBER,
                                            pinCUSTOMER_NAME IN VARCHAR2,
                                            pinPRODUCT_ID    IN NUMBER)
IS
  strNEW_BUSINESS  CUSTOMER.NEW_BUSINESS%TYPE;
  nCUSTOMER_COUNT  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nCUSTOMER_COUNT
    FROM CUSTOMER
    WHERE CUSTOMER_ID = pinCUSTOMER_ID AND
          CUSTOMER_NAME = pinCUSTOMER_NAME;

  INSERT INTO CUSTOMER
    (CUSTOMER_ID, CUSTOMER_NAME, PRODUCT_ID, NEW_BUSINESS)
  VALUES
    (pinCUSTOMER_ID, pinCUSTOMER_NAME, pinPRODUCT_ID
       CASE
         WHEN nCUSTOMER_COUNT = 0 THEN 'New'
         ELSE 'Renewal'
       END);
END INSERT_CUSTOMER;

需要在CUSTOMER中插入值时，使用适当的参数调用 INSERT_CUSTOMER . 根据需要向 INSERT_CUSTOMER 添加逻辑，以强制执行您可能拥有或可能开发的任何其他业务要求 .

祝你好运 .

回复于 2024-04-28T02:41:14+08:00

通过评估其他值[保留]来编写计算列

3 回答

相关问题