选择主值处于禁用状态且子值处于活动状态的行。

Question

我有一个表，其中包含客户协议编号和一个status字段，该字段指示该协议是否有效-1用于活动，0用于禁用。

主客户号码包含5位数字，可以从中达成其他子协议。这些其他协议的特点是10位数，前5来自主数，最后5来自自动生成。

注意，并非所有主要协议都有子协议。

下面是我目前从查询中获得的表的简化片段：

+-------------+----------+------------+--+
|   CustNumber| CustName | CustStatus |  |
+-------------+----------+------------+--+
|12345        | Cust1    |          1 |  |
|1234500001   | Cust1    |          1 |  |
|1234500002   | Cust1    |          0 |  |
|12346        | Cust2    |          0 |  |<---
|1234600001   | Cust2    |          1 |  |<---
|1234600002   | Cust2    |          0 |  |
+-------------+----------+------------+--+

查询：

 SELECT 
     custnumber,
     custstatus,
     custname
  FROM table
  WHERE LEFT(custnumber, 5) IN (
     SELECT LEFT(custnumber, 5)
         FROM   table
         GROUP  BY LEFT(custnumber, 5)
         HAVING Count(*) > 1
      )
  ORDER  BY custnumber,
            custstatus DESC; 

从这里开始我很迷茫。我在想一些类似于子查询的内部连接的东西，但我真的不确定。

我要找的是一个查询，它选择具有子协议号的行，这些行是活动的，但主协议号是禁用的。

我对SQL很陌生，在搜索类似的问题时花了不少时间，但实际上我不知道如何以谷歌友好的方式描述这个问题。

Answer 1

使用WITH子句来实现可读性，但这并不是必要的，并检查状态。

with main_rows as 
(
  select custnumber as main_number, custname, custstatus 
  from mytable 
  where length(custnumber) = 5
)
, sub_rows as 
(
  select 
    left(custnumber, 5) as main_number,
    right(custnumber, 5) as sub_number,
    custname,
    custstatus 
  from mytable 
  where length(custnumber) = 10
)
select
  main_number,
  m.custname as main_name,
  s.sub_number,
  s.custname as sub_name
from main_rows m
join sub_rows s using (main_number)
where m.custstatus = 0 and s.custstatus = 1
order by main_number, s.sub_number;

这里也是一样的事情，但更短，只是没有那么健谈：

select *
from mytable m
join mytable s on s.custnumber like m.custnumber || '_____'
where m.custstatus = 0 and s.custstatus = 1
order by s.custnumber;

Answer 2

2019&fiddle=5873044787e5fd3f32f7648dbc54a7b0

with data (CustNumber, CustName, CustStatus) as(
    Select '12345'        ,'Cust1',1  union all
    Select '1234500001'   ,'Cust1',1  union all
    Select '1234500002'   ,'Cust1',0  union all
    Select '12346'        ,'Cust2',0  union all
    Select '1234600001'   ,'Cust2',1  union all
    Select '1234600002'   ,'Cust2',0
)
,subagg (k,CustNumber, CustName, CustStatus) as(
    select Left(CustNumber,5) k,CustNumber, CustName, CustStatus 
    from data
    where len(CustNumber)=10
    and CustStatus = 1
)
select s.CustNumber ActiveSunCustomer, d.CustNumber InactivePrimaryCustomer
from subagg s
join data d on d.CustNumber=s.k and d.CustStatus = 0