时间戳

首页 > 解决方案 > 将名称添加到周列表

sql - 将名称添加到周列表

问题描述

如何从另一个具有名称的表中将每周的名称列表添加到查询中（按 id 顺序重复）

Weeknumb不同于id 

SELECT DATEPART(wk,DATEADD(wk,t2.number,'2019')) as Weeknumb     
    , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019')
        + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 4) AS StartOfWeek
    , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019')
        + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 11) AS EndOfWeek
FROM master..spt_values t2
WHERE t2.type = 'P'
AND t2.number <= 255
AND YEAR(DATEADD(wk,t2.number,'2019'))=2019

结果：

| Weeknumb |          StartOfWeek |            EndOfWeek |
|----------|----------------------|----------------------|
|        1 | 2019-01-04T00:00:00Z | 2019-01-11T00:00:00Z |
|        2 | 2019-01-11T00:00:00Z | 2019-01-18T00:00:00Z |
|        3 | 2019-01-18T00:00:00Z | 2019-01-25T00:00:00Z |
|        4 | 2019-01-25T00:00:00Z | 2019-02-01T00:00:00Z |
|        5 | 2019-02-01T00:00:00Z | 2019-02-08T00:00:00Z |
|        6 | 2019-02-08T00:00:00Z | 2019-02-15T00:00:00Z |
|        7 | 2019-02-15T00:00:00Z | 2019-02-22T00:00:00Z |
|        8 | 2019-02-22T00:00:00Z | 2019-03-01T00:00:00Z |
|        9 | 2019-03-01T00:00:00Z | 2019-03-08T00:00:00Z |
|       10 | 2019-03-08T00:00:00Z | 2019-03-15T00:00:00Z |
|       11 | 2019-03-15T00:00:00Z | 2019-03-22T00:00:00Z |

我还有一张有名字的桌子

| id | name |
|----|------|
|  1 |    a |
|  2 |    b |
|  3 |    c |

需要：

| Weeknumb |          StartOfWeek |            EndOfWeek | Name
|----------|----------------------|----------------------|-------
|        1 | 2019-01-04T00:00:00Z | 2019-01-11T00:00:00Z | a
|        2 | 2019-01-11T00:00:00Z | 2019-01-18T00:00:00Z | b
|        3 | 2019-01-18T00:00:00Z | 2019-01-25T00:00:00Z | c
|        4 | 2019-01-25T00:00:00Z | 2019-02-01T00:00:00Z | a
|        5 | 2019-02-01T00:00:00Z | 2019-02-08T00:00:00Z | b
|        6 | 2019-02-08T00:00:00Z | 2019-02-15T00:00:00Z | c
|        7 | 2019-02-15T00:00:00Z | 2019-02-22T00:00:00Z | a
|        8 | 2019-02-22T00:00:00Z | 2019-03-01T00:00:00Z | b
|        9 | 2019-03-01T00:00:00Z | 2019-03-08T00:00:00Z | c
|       10 | 2019-03-08T00:00:00Z | 2019-03-15T00:00:00Z | a
|       11 | 2019-03-15T00:00:00Z | 2019-03-22T00:00:00Z | b

标签： sqlsql-server

解决方案

这是你的想法：

select a.Weeknumb
       , a.StartOfWeek
       , a.EndOfWeek
       , b.name from (
       SELECT case when ((ROW_NUMBER() OVER (ORDER BY DATEPART(wk,DATEADD(wk,t2.number,'2019'))) % (select count(1) from testTable2))) = 0 then 3
                   else ((ROW_NUMBER() OVER (ORDER BY DATEPART(wk,DATEADD(wk,t2.number,'2019'))) % (select count(1) from testTable2)))
                   end br
              , DATEPART(wk,DATEADD(wk,t2.number,'2019')) as Weeknumb     
              , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019') + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 4) AS StartOfWeek
              , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019') + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 11) AS EndOfWeek
       FROM master..spt_values t2
       WHERE t2.type = 'P'
       AND t2.number <= 255
       AND YEAR(DATEADD(wk,t2.number,'2019'))=2019) a
left join ( select ROW_NUMBER() OVER (ORDER BY id ) rn, name from  testTable2)b 
on (a.br = b.rn)

这是演示

推荐阅读