SQL Server Replace Union All with joins or recursive CTE

gg58donl 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(98)

Table A
| ID | Employee Name |
| ------------ | ------------ |
| 1 | Ashley |
| 2 | Sam |
| 3 | Samantha |
| 4 | Jessica |
| 5 | Tom |

Table B

ID	Week	Salary
1	week 1	100
2	week 1	50
3	week 1	100
4	week 1	50
1	week 2	100
2	week 2	50
5	week 2	100
2	week 3	20
3	week 3	100
4	week 3	50
5	week 3	100

I want output as:

Table C
| ID | This Week | This week Salary | Next Week | Next week Salary |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | week 1 | 100 | week 2 | 100 |
| 2 | week 1 | 50 | week 2 | 50 |
| 3 | week 1 | 100 | Null | |
| 4 | week 1 | 50 | Null | |
| 5 | Null | | week 2 | 100 |
| 1 | week 2 | 100 | Null | |
| 2 | week 2 | 50 | Week 3 | 20 |
| 3 | Null | | Week 3 | 100 |
| 4 | Null | | Week 3 | 50 |
| 5 | Null | | Week 3 | 100 |
| 1 | NULL | | Week 4 | 20 |
| 2 | Week 3 | 20 | Week 4 | 80 |
| 3 | Week 3 | 100 | Null | |
| 4 | Week 3 | 50 | Null | |
| 5 | Week 3 | 100 | Null | |

I have 14 weeks of data. In order to join 14 weeks of data I might need to do 14 union all. I am looking for an alternative to that

My attempt:

Select A.*, B.* from Table A
Left Outer join Table B      on A.Id = B.ID and B.week = Week 1
Left Outer join Table B AS C on A.Id = C.ID and C.week = Week 2
UNION ALL
Select * from Table A
Left Outer join Table B on A.Id = B.ID and week = Week 2
Left Outer join Table B AS C on A.Id = C.ID and C.week = Week 3

sql-server

来源：https://stackoverflow.com/questions/77354424/replace-union-all-with-joins-or-recursive-cte

2条答案

按热度按时间

ecbunoof1#

There's no need for a recursive CTE here, you can just make a dataset of your distinct weeks and employees. Ideally you should have a separate table for your weeks (if you don't I recommend doing so), but you can use a DISTINCT if not. I also assume your week column is really an int , not a varchar . If it isn't fix that design; week 2 is meant to be before week 10, but with a varchar it would be after, and your design is flawed.

With all that, this gives a solution like thebelow:

WITH EmployeeWeeks AS(
    SELECT DISTINCT
           E.EmployeeID,
           S.Week
    FROM dbo.Employee E
         CROSS JOIN dbo.Salary S)
SELECT EW.EmployeeID,
       S.Week,
       S.Salary,
       LEAD(S.Week) OVER (PARTITION BY EW.EmployeeID ORDER BY EW.Week) AS NextWeek,
       LEAD(S.Salary) OVER (PARTITION BY EW.EmployeeID ORDER BY EW.Week) AS NextSalary
FROM EmployeeWeeks EW
     LEFT JOIN dbo.Salary S ON EW.EmployeeID = S.EmployeeID
                           AND EW.Week = S.Week
ORDER BY EW.[Week],
         EW.EmployeeID;

db<>fiddle
Note, that the sample data given by the OP has no data for week 4, so does not give the expected results in the fiddle, as it's impossible to give data for sample data we're missing.

赞(0）回复(0）举报 2023-11-16

62lalag42#

you can use JOINS to generate the weeks and then perform LEFT JOIN for table B for each week.

赞(0）回复(0）举报 2023-11-16