sql—在3个表中顺序查找多条记录的特定字符串，并按最新条目显示

我有3个表，其中我需要按顺序搜索特定字符串“%sell back%”，并根据我首先找到它的位置获取最新条目。进一步的详细信息列在表3下面，3个表如下所示：
表1：客户

|---------------|---------------|
|  CustomerID   | CustomerName  |
|---------------|---------------|
|    1234       | Johnathan     |
|---------------|---------------|

表2：问题

|---------------|---------------|----------------------|---------------|
|  ProblemID    | CustomerID    | ProblemDescription   | DateReported  |
|---------------|---------------|----------------------|---------------|
|    3203494    | 1234          | Needs Appointment    | 2019-08-01    |
------------------------------------------------------------------------
|    3178766    | 1234          | Sell Back Customer   | 2019-08-12    |
------------------------------------------------------------------------
|    3178765    | 1234          |                      | 2019-08-12    |
|---------------|---------------|----------------------|---------------|

表3：问题事件

|---------------|---------------|----------------------|---------------|
|ProblemEventID | ProblemID     | EventReason          | EventDate     |
|---------------|---------------|----------------------|---------------|
|    1926144    | 3178766       | Reported             | 2019-08-12    | 
------------------------------------------------------------------------
|    2022750    | 3178766       | sell back            | 2019-08-13    |
------------------------------------------------------------------------
|    2022751    | 3178766       | Accepted as sell back| 2019-08-26    |
------------------------------------------------------------------------
|    2022899    | 3178766       | Finalized            | 2019-08-31    |
------------------------------------------------------------------------
|    1926200    | 3178765       | Reported             | 2019-09-15    | 
------------------------------------------------------------------------
|    2022626    | 3178765       | sell back            | 2019-09-20    |
------------------------------------------------------------------------
|    2024500    | 3178765       | Accepted as sell back| 2019-09-30    |
------------------------------------------------------------------------
|    2024501    | 3178765       | Finalized            | 2019-10-05    |
|---------------|---------------|----------------------|---------------|

我要查找的结果应该按照下面列出的步骤顺序提取：
第1步
在问题表的problemdescription列中搜索“%sell back%”字符串（如果存在），然后选择相应的problemid（报告了最新日期）并继续执行步骤#2
第二步
在问题事件表中搜索相应的问题id（在步骤#1中选择），并检查问题事件表的eventreason列中是否存在“%sell back%”字符串，然后选择最新条目并显示如下

|---------------|---------------|------------|-----------------------|-----------------------|------------|
|  CustomerID   | CustomerName  | ProblemId  | ProblemDescription    |      EventReason      | EventDate  |
|---------------|---------------|------------|-----------------------|-----------------------|------------|
|    1234       | Johnathan     | 3178766    | Sell Back Customer    | Accepted as sell back | 2019-08-26 |
|---------------|---------------|------------|-----------------------|-----------------------|------------|

如果对于步骤2，在问题事件表的eventreason列中未找到字符串“%sell back%”，则应显示问题事件表中相应问题ID的最新行
交替顺序：
如果第1步搜索“%sell back%”字符串未产生任何结果，即problemdescription列不包含“%sell back%”字符串，则直接在问题事件表中搜索同一字符串“%sell back%”，然后选择最新条目并按如下方式显示结果：

|---------------|---------------|------------|-----------------------|-----------------------|------------|
|  CustomerID   | CustomerName  | ProblemId  | ProblemDescription    |      EventReason      | EventDate  |
|---------------|---------------|------------|-----------------------|-----------------------|------------|
|    1234       | Johnathan     | 3178765    |                       | Accepted as sell back | 2019-10-05 |
|---------------|---------------|------------|-----------------------|-----------------------|------------|

我正在寻找多个客户记录~10k记录的结果。
非常感谢你的帮助。

您可以使用以下解决方案 row_number . 这是演示。

select 
    subq.customerID,
    customerName,
    problemID,
    problemDescription,
    eventReason,
    eventDate
from
(
  select 
    customerID,      
    p.problemID,
    problemDescription,
    eventReason,
    eventDate,
    count(*) over (partition by p.problemID) as cnt,
    row_number() over (partition by p.problemID order by DateReported desc) as pbrn,
    row_number() over (partition by pe.problemID order by eventDate desc) as pern
  from problem p
  join problemEvent pe
  on p.problemID = pe.problemID
  where (problemDescription like '%sell back%'
  and eventReason like '%sell back%') 
  or eventReason like '%sell back%'
) subq
join customer c
on subq.customerID = c.customerID
where (cnt = 1
and pbrn = 1)
or pern = 1

输出：


* -------------------------------------------------------------------------------------------------*

| customerID    customerName    problemID   problemDescription  eventReason             eventDate |

* -------------------------------------------------------------------------------------------------*

|   1234         Johnathan       3178766    Sell Back Customer  Accepted as sell back  2019-08-26 |

* -------------------------------------------------------------------------------------------------*

sql—在3个表中顺序查找多条记录的特定字符串，并按最新条目显示

1条答案

相关问题

热门标签

最新问答