引自免费微信小程序:皆我百晓生
为了将表A转换为表B的结果,你需要根据Formweek
列中的值进行分组,并在每个组内重新设置ondays
列的值。由于Formweek
的格式是月/日,我们可以将其转换为日期类型,然后通过计算与当前日期之间的差距得到新的一周的第一天。接下来,我们将这一周的第一天作为基准,对每个日期计算它在一周内的位置(即ondays
列的值)。
以下是详细的SQL查询步骤:
- 首先,我们需要创建一个辅助函数,用于将'Formweek'字符串转换为日期。在这个例子中,我们将假设'Formweek'总是表示月份的最后一周的星期一。例如,'12/23'表示12月的第23天,这通常是12月最后一周的星期一。
CREATE FUNCTION dbo.FormweekToDateTime (@formweek NVARCHAR(5))
RETURNS DATETIME
AS
BEGIN
DECLARE @year INT, @month INT, @day INT;
SET @year = SUBSTRING(@formweek, 1, 4);
SET @month = SUBSTRING(@formweek, 6, 2);
SET @day = CAST(SUBSTRING(@formweek, 3, 2) AS INT);
RETURN DATEFROMPART(year=@year, month=@month, day=@day);
END;
- 然后,我们使用这个函数来更新
ondays
列。首先,我们需要找到每个Formweek
对应的起始日期(即那一周的星期一),然后计算每个日期与起始日期之间的差值,最后除以7并向上取整得到新的一周的天数。
UPDATE A
SET A.ondays = DATEDIFF(DAY, dbo.FormweekToDateTime(Formweek), Date) / 7 + 1
FROM TableA A;
- 最后,我们删除不需要的行,只保留那些
Formweek
与Date
所在的月份不同的行。这里我们使用窗口函数ROW_NUMBER()
来实现:
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Formweek ORDER BY Date) AS rn
FROM TableA
)
DELETE FROM CTE WHERE rn > 1;
这样,表A就被转换成了类似表B的结果。请注意,这个过程依赖于Formweek
总是表示当月最后一周的星期一的假设。如果你的数据不满足这个条件,可能需要调整转换逻辑。
以下是完整的SQL脚本:
CREATE FUNCTION dbo.FormweekToDateTime (@formweek NVARCHAR(5))
RETURNS DATETIME
AS
BEGIN
DECLARE @year INT, @month INT, @day INT;
SET @year = SUBSTRING(@formweek, 1, 4);
SET @month = SUBSTRING(@formweek, 6, 2);
SET @day = CAST(SUBSTRING(@formweek, 3, 2) AS INT);
RETURN DATEFROMPART(year=@year, month=@month, day=@day);
END;
UPDATE A
SET A.ondays = DATEDIFF(DAY, dbo.FormweekToDateTime(Formweek), Date) / 7 + 1
FROM TableA A;
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Formweek ORDER BY Date) AS rn
FROM TableA
)
DELETE FROM CTE WHERE rn > 1;
执行这段代码后,表A应该会变为类似于表B的结果。