SQL Server How can I simplify SQL query using Join?

zed5wv10 于 5个月前发布在其他

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

I'm trying to created a simpler way for the query below.

SELECT 
    T1.company,
    T1.project, 
    T1.ID, 
    T1.date,
    T1.qty, 
    (
        SELECT MAX(T2.cost)
        FROM Hour_Cost  AS T2
        WHERE T2.company = T1.company AND
        T2.id = T1.ID AND
        T2.trans_date =
        (
            SELECT MAX(T3.date)
            FROM Hour_Cost AS T3
            WHERE T3.company = T2.company AND
            T3.id = T2.id AND
            T3.trans_date <= T1.date
        )
    ) AS PRICE
FROM Empl_Table AS T1

The query takes long hours to get the data I need. Is there a way to simplify this? I am trying to use joins but can't get the exact data I need.

Thank you for your help!

See below sample data.

Empl_Table:

id  date
1   5/31/2015
1   11/15/2015
1   11/15/2015
1   11/15/2015
1   12/22/2015
1   12/24/2015
1   12/25/2015
1   1/1/2016
1   1/5/2016
1   1/11/2016
1   1/18/2016
1   1/25/2016
1   4/15/2016
1   4/27/2016
2   10/5/2018
2   10/8/2018
2   10/9/2018
2   10/10/2018
2   10/11/2018
2   10/12/2018
2   2/1/2019
2   2/4/2019
2   2/5/2019
2   2/6/2019
2   2/7/2019
2   2/8/2019
2   2/11/2019
2   2/12/2019
2   2/13/2019
2   2/14/2019
2   2/15/2019

Hour_Cost:

date    id  cost
12/1/2015   1   79.53
1/1/2016    1   64.49
1/1/2018    2   69.59
1/1/2019    2   62.45
1/1/2020    2   60.37
1/1/2021    2   63.79

Desired results:

ID  date        OUTPUT
1   5/31/2015   0.00
1   11/15/2015  0.00
1   11/15/2015  0.00
1   11/15/2015  0.00
1   12/22/2015  79.53
1   12/24/2015  79.53
1   12/25/2015  79.53
1   1/1/2016    64.49
1   1/5/2016    64.49
1   1/11/2016   64.49
1   1/18/2016   64.49
1   1/25/2016   64.49
1   4/15/2016   64.49
1   4/27/2016   64.49
2   10/5/2018   69.59
2   10/8/2018   69.59
2   10/9/2018   69.59
2   10/10/2018  69.59
2   10/11/2018  69.59
2   10/12/2018  69.59
2   2/1/2019    62.45
2   2/4/2019    62.45
2   2/5/2019    62.45
2   2/6/2019    62.45
2   2/7/2019    62.45
2   2/8/2019    62.45
2   2/11/2019   62.45
2   2/12/2019   62.45
2   2/13/2019   62.45
2   2/14/2019   62.45
2   2/15/2019   62.45

thanks

sql-server

来源：https://stackoverflow.com/questions/77604203/how-can-i-simplify-sql-query-using-join

1条答案

按热度按时间

8fq7wneg1#

You can use the OUTER APPLY(SELECT TOP 1 ... ORDER BY ...) pattern to directly select the best matching Hour_Cost row for each Empl_Table row.

The CROSS APPLY and OUTER APPLY constructs are like joins to subselects. A CROSS APPLY is like an inner join, while an OUTER APPLY is like a left outer join.

SELECT 
    E.company,
    E.project, 
    E.ID, 
    E.date,
    E.qty, 
    HC.cost AS PRICE
FROM Empl_Table AS E
OUTER APPLY (
    SELECT TOP 1 HC.*
    FROM Hour_Cost AS HC
    WHERE HC.company = E.company
    AND HC.id = E.id
    AND HC.trans_date <= E.date
    ORDER BY HC.trans_date DESC
) AS HC

This assumes that Hour_Cost table does not have conflicting rows with the same company , id , and trans_date .

For efficiency, you should ensure that the Hour_Cost table has an appropriate index, such as Hour_Cost(company, id, trans_date) . Add INCLUDE(cost) to the index definition to make it a covering index for even better performance.

Results:
| ID | date | PRICE |
| ------------ | ------------ | ------------ |
| 1 | 2015-05-31 | null |
| 1 | 2015-11-15 | null |
| 1 | 2015-11-15 | null |
| 1 | 2015-11-15 | null |
| 1 | 2015-12-22 | 79.53 |
| 1 | 2015-12-24 | 79.53 |
| 1 | 2015-12-25 | 79.53 |
| 1 | 2016-01-01 | 64.49 |
| 1 | 2016-01-05 | 64.49 |
| 1 | 2016-01-11 | 64.49 |
| 1 | 2016-01-18 | 64.49 |
| 1 | 2016-01-25 | 64.49 |
| 1 | 2016-04-15 | 64.49 |
| 1 | 2016-04-27 | 64.49 |
| 2 | 2018-10-05 | 69.59 |
| 2 | 2018-10-08 | 69.59 |
| 2 | 2018-10-09 | 69.59 |
| 2 | 2018-10-10 | 69.59 |
| 2 | 2018-10-11 | 69.59 |
| 2 | 2018-10-12 | 69.59 |
| 2 | 2019-02-01 | 62.45 |
| 2 | 2019-02-04 | 62.45 |
| 2 | 2019-02-05 | 62.45 |
| 2 | 2019-02-06 | 62.45 |
| 2 | 2019-02-07 | 62.45 |
| 2 | 2019-02-08 | 62.45 |
| 2 | 2019-02-11 | 62.45 |
| 2 | 2019-02-12 | 62.45 |
| 2 | 2019-02-13 | 62.45 |
| 2 | 2019-02-14 | 62.45 |
| 2 | 2019-02-15 | 62.45 |

See this db<>fiddle for a demo.

赞(0）回复(0）举报 5个月前

我来回答

SQL Server How can I simplify SQL query using Join?

1条答案

相关问题

热门标签

最新问答