代码之家 › 专栏 › 技术社区 › user3369545

添加名称列并为每个名称分配5个会话ID

tsql sql-server

user3369545 · 技术社区 · 6 年前

我在下面有一张名为customers-DDL的表格

CREATE TABLE customers
  (
    sessionID  INT
   ,customerID VARCHAR(100)
  );

INSERT INTO customer
VALUES
  (10435, 'RTE')
 ,(2312, 'RTE')
 ,(2980, 'RTE')
 ,(1365, 'RTE')
 ,(1106, 'RTE')
 ,(9682, 'RTE')
 ,(5779, 'RTE')
 ,(3609, 'GTE')
 ,(2881, 'GTE')
 ,(4197, 'GTE')
 ,(2905, 'GTE')
 ,(6390, 'GTE')
 ,(4514, 'GTE')
 ,(7617, 'GTE')
 ,(7138, 'GTE')
 ,(5927, 'GTE')
 ,(397, 'GTE')
 ,(5949, 'LOA')
 ,(4324, 'LOA')
 ,(7399, 'LOA')
 ,(1459, 'LOA')
 ,(2016, 'LOA')
 ,(6650, 'LOA')
 ,(7562, 'LOA')
 ,(9057, 'nma')
 ,(3066, 'nma')
 ,(9184, 'nma')
 ,(8042, 'nma')
 ,(2348, 'nma')
 ,(9755, 'nma')
 ,(6770, 'nma')
 ,(661, 'nma')
 ,(4084, 'nma')
 ,(4991, 'nma')
 ,(8822, 'nma')
 ,(3632, 'nma')
 ,(2296, 'nma');

 SELECT
  @Report1WeeklyBody1 =
(
  SELECT
    TOP 20
    TD = sessionID
   ,TD = CONVERT(NVARCHAR(500), customerid)
   ,TD = @URL + sessionID
  FROM
    (
      SELECT
        DISTINCT
        sessionID
       ,customerID
      FROM
        customers
      WHERE
        CONVERT(DATETIME, dateofevent, 111) BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), '04:00:00:001') AND DATEADD(d, DATEDIFF(d, -1, GETDATE()), '03:59:59:999')
      GROUP BY
        sessionID
       ,customerid
    ) AS A
  ORDER BY
    NEWID()
  FOR XML RAW('tr'), ELEMENTS
);

现在,我必须修改上述存储过程以添加新的列名,并将5个不同的会话ID分别分配给以下名称:

麦克斯,艾伦,艾米,乔希,苏西尔

这样,每天就可以发送给这五个名称分配了25个会话ID的整个HTML表

有人能帮忙吗。

我正在使用SQL server 2017

1 回复 | 直到 6 年前

Andrey Nikolov user3098458 6 年前

首先,将top子句移到内部select并将行数更改为25,然后在内部select中添加一个新列以显示行数。在outer选择check the row number,并根据范围(1..5、6..10等)返回不同的person。

最后,select语句应该如下所示:

SELECT
  @Report1WeeklyBody1 =
(
  SELECT
    TD = sessionID
   ,TD = CONVERT(NVARCHAR(500), customerid)
   ,TD = @URL + sessionID
   ,TD = case when RowNo <= 5 then 'Max'
            when RowNo <= 10 then 'Allen'
            when RowNo <= 15 then 'Amy'
            when RowNo <= 20 then 'Josh'
            when RowNo <= 25 then 'Suzier' end
  FROM
    (
      SELECT
        DISTINCT TOP 25
        sessionID
       ,customerID
       , ROW_NUMBER() over(order by newid()) as RowNo
      FROM
        @customers
      WHERE
        CONVERT(DATETIME, dateofevent, 111) BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), '04:00:00:001') AND DATEADD(d, DATEDIFF(d, -1, GETDATE()), '03:59:59:999')
      GROUP BY
        sessionID
       ,customerid
    ) AS A
  ORDER BY
    NEWID()
  FOR XML RAW('tr'), ELEMENTS
);