sql如何将一行拆分为多行

问题描述

我们有一个表格，我想将该表格中的一行拆分为多行.如果你看到下面.那是我桌子上的一行.我们正在计算资源加载，我想将下面的这一行拆分到如下图所示的结果表中.

We have a table and I want to split a row in that table into multiple rows. If you see below. That is one row from my table. We are calculating resource loading and I want to split this row below into the result table also shown in the image below.

我将负责计算周开始日期和小时数，只需告诉我如何在 sql server 2005 中将一行拆分为多行.

I will take care of calculating the week start date and hours, just tell me how can I split a row into multiple rows in sql server 2005.

在这个例子中，大卫从 3 月 3 日到 3 月 19 日工作了 25 个小时.

Here in this example David worked from march 3rd to march 19 for 25 hours.

这意味着如果我们将其除以工作周数，则为 2 周.

Which means if we divide this by number of weeks worked, comes to 2 weeks.

从 3 月 7 日开始的一周到 3 月 11 日结束的一周，然后是 3 月 14 日开始的一周到 3 月 18 日结束的一周.

From week starting march 7 to week ending march 11, and then week starting march 14th to week end march 18th.

根据工作周数与工作天数之比来划分小时数.

The hours are split based on number of weeks worked in ratio of number of days worked.

推荐答案

你可以做的是有一个像

What you can do is have a table with week entries like

CREATE TABLE WeeKDays ( WeekStartDate date, WeekEndDate date);

现在用这个来加入你的桌子

Now JOIN your table with this one like

SELECT 
 Task_ID,Name, WeekStartDate, WeekEndDate
-- Add logic for splitting hours
, (hours/workdays)* 
 CASE 
     WHEN  (T.StartDate >= W.WeekStartDate AND T.StartDate <= W.WeekEndDate) THEN DATEDIFF(d,T.StartDate,W.WeekEndDate)
     WHEN  (T.EndDate >= W.WeekStartDate AND T.EndDate <= W.WeekEndDate) THEN DATEDIFF(d,W.WeekStartDate,T.StartDate)
     WHEN  (T.StartDate <W.WeekStartDate AND T.EndDate>W.WeeKEndDate) THEN 7
--assuming 7 working days
END as Hours

FROM Tasks T LEFT JOIN WeekDays W ON 
(T.StartDate >= W.WeekStartDate AND T.StartDate <= W.WeekEndDate)  
 OR (T.EndDate >= W.WeekStartDate AND T.EndDate <= W.WeekEndDate) 
 OR (T.StartDate <W.WeekStartDate AND T.EndDate>W.WeeKEndDate)