SQL查询新增Dummy列：按客户邮箱条数规则赋值A/B

原查询语句 #

SELECT hp.party_name
      ,hzl.location_id "Bill To Number"
      ,hzps.party_site_name "Customer Bill To Name"
      ,hcp.email_address "Customer Email Address"
FROM 
    hz_parties hp,
    hz_cust_accounts hca, 
    hz_cust_acct_sites_all hcsa, 
    hz_cust_site_uses_all hcsu,
    hz_party_sites hzps ,
    hz_locations hzl,
    hz_cust_account_roles hcar,
    hz_contact_points hcp, 
    hz_relationships hr     
WHERE 1=1
and hp.party_id             = hca.party_id
and hca.cust_account_id     = hcsa.cust_account_id
and hcsa.cust_acct_site_id  = hcsu.cust_acct_site_id  
and hcsa.party_site_id      = hzps.party_site_id(+)
and hzps.location_id        = hzl.location_id(+)
and hcsu.site_use_code      = 'BILL_TO'
and NVL(hcar.status,'A')    ='A'
and hcar.cust_account_id    = hca.cust_account_id
and hcar.cust_acct_site_id  = hcsa.cust_acct_site_id
and hcar.relationship_id    = hcp.relationship_id
and hcp.contact_point_type  = 'EMAIL'
and nvl(hcp.end_date,SYSDATE+5) > SYSDATE
and hcp.relationship_id     = hr.relationship_id 
and hr.relationship_code    = 'CONTACT_OF' 
and hr.object_id            = hp.party_id;

原查询结果 #

PARTY_NAME  Bill_To_Number  Customer_Bill_To_Name   Customer_Email_Address
Party 1         1006746009  CUSTOM PRODUCTS         newton@testemail.net
Party 1         1006746009  CUSTOM PRODUCTS         lorne@testemail.net
Party 2         1006746010  PAINT COMPANY           tgliwell@testemail.com
Party 2         1006746010  PAINT COMPANY           APAL@testemail.com
Party 3         1006746011  ADVANCED MATERIALS  invo@testemail.com
Party 4         1006746012  ADVANCED PRODUCTS   accpayable@testemail.com

需求说明 #

新增名为Dummy的列，赋值规则：

• 以party_name为分组依据，同一客户的第一条记录赋值'A'，第二条赋值'B'
• 客户仅有一个邮箱时，赋值'A'

解决方案 #

使用Oracle的ROW_NUMBER()窗口函数按party_name分组排序，再通过CASE语句生成Dummy列：

SELECT hp.party_name
      ,hzl.location_id "Bill To Number"
      ,hzps.party_site_name "Customer Bill To Name"
      ,hcp.email_address "Customer Email Address"
      ,CASE 
          WHEN row_num = 1 THEN 'A'
          WHEN row_num = 2 THEN 'B'
          ELSE 'A' -- 若有超过2个邮箱，默认赋值'A'，可按需调整
       END AS "Dummy"
FROM (
    SELECT hp.party_name
          ,hzl.location_id
          ,hzps.party_site_name
          ,hcp.email_address
          ,ROW_NUMBER() OVER (PARTITION BY hp.party_name ORDER BY hcp.email_address) AS row_num
    FROM 
        hz_parties hp,
        hz_cust_accounts hca, 
        hz_cust_acct_sites_all hcsa, 
        hz_cust_site_uses_all hcsu,
        hz_party_sites hzps ,
        hz_locations hzl,
        hz_cust_account_roles hcar,
        hz_contact_points hcp, 
        hz_relationships hr     
    WHERE 1=1
    and hp.party_id             = hca.party_id
    and hca.cust_account_id     = hcsa.cust_account_id
    and hcsa.cust_acct_site_id  = hcsu.cust_acct_site_id  
    and hcsa.party_site_id      = hzps.party_site_id(+)
    and hzps.location_id        = hzl.location_id(+)
    and hcsu.site_use_code      = 'BILL_TO'
    and NVL(hcar.status,'A')    ='A'
    and hcar.cust_account_id    = hca.cust_account_id
    and hcar.cust_acct_site_id  = hcsa.cust_acct_site_id
    and hcar.relationship_id    = hcp.relationship_id
    and hcp.contact_point_type  = 'EMAIL'
    and nvl(hcp.end_date,SYSDATE+5) > SYSDATE
    and hcp.relationship_id     = hr.relationship_id 
    and hr.relationship_code    = 'CONTACT_OF' 
    and hr.object_id            = hp.party_id
) t;

逻辑说明 #

1. 子查询生成行号：通过ROW_NUMBER() OVER (PARTITION BY hp.party_name ORDER BY hcp.email_address)，按party_name分组，每组内按邮箱地址排序（可替换为创建时间等其他字段），生成从1开始的连续行号。
2. CASE语句赋值：根据行号判断，第1行赋值'A'，第2行赋值'B'，超过2条的记录默认给'A'，可根据实际需求修改规则。

执行后将得到符合要求的结果：

PARTY_NAME  Bill_To_Number  Customer_Bill_To_Name   Customer_Email_Address   Dummy
Party 1         1006746009  CUSTOM PRODUCTS         newton@testemail.net     A
Party 1         1006746009  CUSTOM PRODUCTS         lorne@testemail.net      B
Party 2         1006746010  PAINT COMPANY           tgliwell@testemail.com   A
Party 2         1006746010  PAINT COMPANY           APAL@testemail.com       B
Party 3         1006746011  ADVANCED MATERIALS  invo@testemail.com       A
Party 4         1006746012  ADVANCED PRODUCTS   accpayable@testemail.com A

内容的提问来源于stack exchange，提问作者Anji007