连接3个表；从第三个表行中选择，该行按最新日期包含特定字符串

ilmyapht 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(316)

我有3个表，其中我需要从第3个表中搜索此特定字符串“%sell back%”，并从该表中获取最新条目，3个表如下所示：
表1：客户

|---------------|---------------|
|  Customer ID  | CustomerName  |
|---------------|---------------|
|    1234       | Johnathan     |
---------------------------------

表2：问题

|---------------|---------------|----------------------|---------------|
|  Problem ID   | CustomerID    | Problem Description  | Date Reported |
|---------------|---------------|----------------------|---------------|
|    3203494    | 1234          | Needs Appointment    | 2019-08-01    |
------------------------------------------------------------------------
|    3178766    | 1234          | Sell Back Customer   | 2019-08-12    |
------------------------------------------------------------------------

表3：问题事件

|---------------|---------------|----------------------|---------------|
|ProblemEventID | Problem ID    | Event Reason         | Event Date    |
|---------------|---------------|----------------------|---------------|
|    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    |
------------------------------------------------------------------------

我想要的结果如下：

|---------------|---------------|------------|-----------------------|------------|
|  Customer ID  | CustomerName  | Problem Id | Event Reason          | Event Date |
|---------------|---------------|------------|-----------------------|------------|
|    1234       | Johnathan     | 3178766    | Accepted as sell back | 2019-08-26 |
---------------------------------------------------------------------|------------|

我们很感激你的帮助。

sql Join sql-server select sql-server-2008-r2

来源：https://stackoverflow.com/questions/62354705/join-3-tables-select-from-third-table-row-which-contains-specific-string-by-lat

1条答案

按热度按时间

v09wglhw1#

如果您希望在整个表中显示最新的“卖出”事件，则可以加入、订购和限制：

select top (1)
    c.customer_id, 
    c.customer_name
    p.problem_id,
    pe.event_reason,
    pe.event_date
from customer c
inner join problem p on p.customer_id = c.customer_id
inner join problem_event pe on pe.problem_id = p.problem_id
where pe.event_reason like '%sell back%'
order by pe.event_date

还有其他方法可以理解你的问题。假设您希望每个客户都有最新的“返销”活动，那么这就是每个组最大的问题。你可以用 row_number() :

select *
from (
    select
        c.customer_id, 
        c.customer_name
        p.problem_id,
        pe.event_reason,
        pe.event_date,
        row_number() over(partition by c.customer_id order by pe.event_date desc) rn
    from customer c
    inner join problem p on p.customer_id = c.customer_id
    inner join problem_event pe on pe.problem_id = p.problem_id
    where pe.event_reason like '%sell back%'
) t
where rn = 1
order by c.customer_id

每个问题返回最新的“卖出”事件的练习，这是对您的问题的最后可能的解释，留给读者！

赞(0）回复(0）举报 2021-08-09

我来回答

连接3个表；从第三个表行中选择，该行按最新日期包含特定字符串

1条答案

相关问题

热门标签

最新问答