logo标签列表

在SQL Server 2008中使用日期函数进行计算

sqlsql-serversql-server-2008
14

我正在尝试为一组BINGID、INDUSID和COMP1计算TO_DATE列。

当IsRowActive = 1时，TO_DATE = "9999-12-31"，这个值被正确返回。

但是当IsRowActive = 0时，我们需要计算To_Date，它应该比下一个FROMDT少1秒钟。

数据：

    DECLARE @MYTABLE TABLE

(
BINGID INT,
INDUSID INT,
DTSEARCH DATETIME2,
COMP1 VARCHAR (100),
LISTPRICE NUMERIC(10,2),
FROMDT DATETIME2,
IsRowActive INT

)
INSERT @MYTABLE

SELECT 1002285, 1002,   '2016-03-03 04:10:58.0000000',  '0026PU009163-031', '77.7600',  '2015-12-19 12:51:49.0000000',0 UNION ALL
SELECT 1002285, 1002,   '2016-05-27 12:14:53.0000000',  '0026PU009163-031', '85.2200',  '2016-05-27 12:14:53.0000000',0 UNION ALL
SELECT 1002285, 1002,   '2016-07-20 06:44:37.0000000',  '0026PU009163-031', '90.3900',  '2016-07-20 06:44:37.0000000',0 UNION ALL
SELECT 1002285, 1002,   '2016-11-09 13:37:13.0000000',  '0026PU009163-031', '131.4500', '2016-10-18 13:49:10.0000000',1 UNION ALL
SELECT 1002285, 1002,   '2015-12-19 12:51:41.0000000',  '10122374', 65.1400,    '2015-12-19 12:51:41.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-03-03 04:11:01.0000000',  '10122374', 117.2100,   '2016-03-03 04:11:01.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-05-27 12:14:45.0000000',  '10122374', 53.5500,    '2016-05-27 12:14:45.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-07-20 06:44:29.0000000',  '10122374', 48.5000,    '2016-07-20 06:44:29.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-10-18 13:49:00.0000000',  '10122374', 75.6800,    '2016-10-18 13:49:00.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-11-09 13:37:02.0000000',  '10122374', 68.2400,    '2016-11-09 13:37:02.0000000',  1 UNION ALL

SELECT 1000001, 1002,   '2016-03-03 02:22:09.0000000',  '161GDB1577',   37.1700,    '2015-12-18 06:45:05.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-03-03 02:22:18.0000000',  '0392347402',   41.9100,    '2015-12-18 06:45:14.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-05-26 14:54:28.0000000',  '161GDB1577',   46.7100,    '2016-05-26 14:54:28.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-05-26 14:54:42.0000000',  '0392347402',   54.7100,    '2016-05-26 14:54:42.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-07-15 06:34:33.0000000',  '161GDB1577',   52.4800,    '2016-07-15 06:34:33.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-07-15 06:34:45.0000000',  '0392347402',   81.7100,    '2016-07-15 06:34:45.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-10-17 11:26:45.0000000',  '161GDB1577',   61.6400,    '2016-10-17 11:26:45.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-11-09 02:21:17.0000000',  '0392347402',   81.9200,    '2016-10-17 11:26:58.0000000',1  UNION ALL
SELECT 1000001, 1002,   '2016-11-09 02:21:05.0000000',  '161GDB1577',   78.3500,    '2016-11-09 02:21:05.0000000',1  UNION ALL
SELECT 1000005, 1002,   '2018-11-09 02:21:05.0000000',  '556556GHB',    78.3500,    '2018-11-09 02:21:05.0000000',1

我尝试过的查询 - 不幸的是它返回了错误的数据:

SELECT
    BINGID, INDUSID, DTSEARCH,  
    COMP1, LISTPRICE, FROMDT,
    CASE 
       WHEN IsRowActive = 1 
          THEN '9999-12-31' 
          ELSE TO_DATE 
    END AS TO_DATE,
    IsRowActive
FROM 
    @MYTABLE mt
OUTER APPLY 
    (SELECT  
         MAX(DATEADD(second, -1, FROMDT)) TO_DATE 
     FROM   
         @MYTABLE mt2 
     WHERE   
         mt2.BINGID = mt.BINGID 
         AND mt2.INDUSID = mt.INDUSID 
         AND mt2.FROMDT > mt.FROMDT) oa
WHERE 
    mt.INDUSID = '1002'

预期输出

  BINGID    INDUSID DTSEARCH    COMP1   LISTPRICE   FROMDT  NEW_TO_DATE IsRowCurrent
1000001 1002    2016-03-03 02:22:09.0000000 161GDB1577  37.1700 2015-12-18 06:45:05.0000000 2016-05-26 14:54:27.0000000 0
1000001 1002    2016-03-03 02:22:18.0000000 0392347402  41.9100 2015-12-18 06:45:14.0000000 2016-05-26 14:54:41.0000000 0
1000001 1002    2016-05-26 14:54:28.0000000 161GDB1577  46.7100 2016-05-26 14:54:28.0000000 2016-07-15 06:34:32.0000000 0
1000001 1002    2016-05-26 14:54:42.0000000 0392347402  54.7100 2016-05-26 14:54:42.0000000 2016-07-15 06:34:44.0000000 0
1000001 1002    2016-07-15 06:34:33.0000000 161GDB1577  52.4800 2016-07-15 06:34:33.0000000 2016-10-17 11:26:44.0000000 0
1000001 1002    2016-07-15 06:34:45.0000000 0392347402  81.7100 2016-07-15 06:34:45.0000000 2016-10-17 11:26:57.0000000 0
1000001 1002    2016-10-17 11:26:45.0000000 161GDB1577  61.6400 2016-10-17 11:26:45.0000000 2016-11-09 02:21:04.0000000 0
1000001 1002    2016-11-09 02:21:17.0000000 0392347402  81.9200 2016-10-17 11:26:58.0000000 9999-12-31 00:00:00.0000000 1
1000001 1002    2016-11-09 02:21:05.0000000 161GDB1577  78.3500 2016-11-09 02:21:05.0000000 9999-12-31 00:00:00.0000000 1
1000005 1002    2018-11-09 02:21:05.0000000 556556GHB   78.3500 2018-11-09 02:21:05.0000000 9999-12-31 00:00:00.0000000 1
1002285,    1002,   '2016-03-03 04:10:58.0000000',  '0026PU009163-031', '77.7600',  2015-12-19 12:51:49.0000000'    2016-05-27 12:14:52.0000000'    0
1002285,    1002,   '2016-05-27 12:14:53.0000000',  '0026PU009163-031', '85.2200',  2016-05-27 12:14:53.0000000'    2016-07-20 06:44:36.0000000'    0
1002285,    1002,   '2016-07-20 06:44:37.0000000',  '0026PU009163-031', '90.3900',  2016-07-20 06:44:37.0000000'    2016-10-18 13:49:09.0000000'    0
1002285,    1002,   '2016-11-09 13:37:13.0000000',  '0026PU009163-031', '131.4500', 2016-10-18 13:49:10.0000000'    9999-12-31 00:00:00.0000000 1
1002285,    1002,   '2015-12-19 12:51:41.0000000',  '10122374', 65.1400,    '2015-12-19 12:51:41.0000000',  2016-03-03 04:11:00.0000000',   0
1002285,    1002,   '2016-03-03 04:11:01.0000000',  '10122374', 117.2100,   '2016-03-03 04:11:01.0000000',  2016-05-27 12:14:44.0000000',   0
1002285,    1002,   '2016-05-27 12:14:45.0000000',  '10122374', 53.5500,    '2016-05-27 12:14:45.0000000',  2016-07-20 06:44:28.0000000',   0
1002285,    1002,   '2016-07-20 06:44:29.0000000',  '10122374', 48.5000,    '2016-07-20 06:44:29.0000000',  2016-10-18 13:48:59.0000000',   0
1002285,    1002,   '2016-10-18 13:49:00.0000000',  '10122374', 75.6800,    '2016-10-18 13:49:00.0000000',  2016-11-09 13:37:01.0000000',   0
1002285,    1002,   '2016-11-09 13:37:02.0000000',  '10122374', 68.2400,    '2016-11-09 13:37:02.0000000',  9999-12-31 00:00:00.0000000 1

谢谢。

5
好的，我会尽力进行翻译并使其更加易懂。以下是需要翻译的内容：“Btw: This is a good question! Copy'n'pasteable test scenario, own effort, expected result... +1 from my side”。 - Shnugo
@Rohini Mathur 预期输出需要任何按顺序排列或类似的内容。否则，我得到了结果，但排序顺序不匹配。 - Mr. Bhosale
5个回答
2
尝试这个简单易懂的解决方案，使用CTE和自连接。
with cte as  
( 
  SELECT
  ROW_NUMBER() over (order by BINGID,INDUSID,DTSEARCH,COMP1,LISTPRICE,FROMDT) 
      as rowno, -- It is good if you have identity column here
      BINGID,
      INDUSID,    
      DTSEARCH,   
      COMP1,  
      LISTPRICE,  
      FROMDT,
      IsRowActive
      FROM @MYTABLE mt 
)
select c1.*,
 CASE WHEN c1.IsRowActive = 1 THEN '9999-12-31' ELSE DATEADD(second, -1, c2.FROMDT) END
 AS TO_DATE
 from cte c1 left join cte c2
 on c1.rowno+1 = c2.rowno
投票了。尝试简单的解决方案，但结果仍然不匹配。 - Mr. Bhosale
请查看下面评论中列出的另一个样本数据。 - Mr. Bhosale
“order by BINGID,INDUSID,DTSEARCH,COMP1,LISTPRICE,FROMDT” 不可能是正确的。 - Vladimir Baranov
是的，但是……查询显示了正确的结果。同时提到——如果您在此处有标识列，那就太好了。 - Munavvar
@Munavvar，实际上不是这样的。你的查询没有返回预期的结果。这就是为什么会被踩的原因。提示：最好还是加上PARTITION BY。 - Vladimir Baranov
2
我非常喜欢@Chanukya的回答。但是，由于您使用的是2008版本，因此无法使用LEAD函数。相反，您可以使用自连接：
-- SQL Server 2008.
SELECT
    c.*,
    CASE c.IsRowActive 
        WHEN 1 THEN '9999-12-31'
        ELSE DATEADD(SECOND, -1, MIN(p.FROMDT))
    END AS TO_DATE
FROM
    @MYTABLE AS c
        LEFT OUTER JOIN @MYTABLE AS p       ON  p.BINGID    = c.BINGID
                                            AND p.INDUSID   = c.INDUSID
                                            AND p.FROMDT    > c.FROMDT
GROUP BY
    c.BINGID,
    c.INDUSID,
    c.DTSEARCH,
    c.COMP1,
    c.LISTPRICE,
    c.FROMDT,
    c.IsRowActive
ORDER BY
    c.FROMDT
;

逻辑类似于您的外部应用程序，但效果应该更好。这是因为没有相关性。所示样本数据带来了一些挑战。因为有两个记录的FROMDT为2016-07-20 06:44:37.0000000，您可以认为我的结果是错误的。
谢谢@VladimirBaranov。你说得对，我的尝试很糟糕。更新以修复。喜欢你的答案，写得很好，易于理解。 - David Rushton
1
DECLARE @MYTABLE TABLE
(
BINGID  int,
INDUSID int,
DTSEARCH datetime2, 
COMP1 varchar(100),
LISTPRICE numeric(15,5),    
FROMDT  datetime2,
IsRowActive int

)
insert @MYTABLE

SELECT 1002285  ,1002   ,'2016-03-03 04:10:58.0000000', '0026PU009163-031', 77.7600 ,'2015-12-19 12:51:49.0000000', 0 UNION ALL
SELECT 1002285  ,1002   ,'2016-05-27 12:14:53.0000000', '0026PU009163-031', 85.2200 ,'2016-05-27 12:14:53.0000000', 0  UNION ALL
SELECT 1002285  ,1002   ,'2016-07-20 06:44:37.0000000', '0026PU009163-031', 90.3900 ,'2016-07-20 06:44:37.0000000', 0  UNION ALL
SELECT 1002285  ,1002   ,'2016-11-09 13:37:13.0000000', '0026PU009163-031', 131.4500,'2016-07-20 06:44:37.0000000', 1


select BINGID,DTSEARCH,COMP1,LISTPRICE,FROMDT,CASE WHEN IsRowActive = 0 THEN lead(DATEADD(SS,-1,FROMDT)) OVER (ORDER BY FROMDT) ELSE  '9999-12-31' END AS expected_date

FROM @MYTABLE mt

输出

BINGID  DTSEARCH    COMP1   LISTPRICE   FROMDT  expected_date
1002285 2016-03-03 04:10:58.0000000 0026PU009163-031    77.76000    2015-12-19 12:51:49.0000000 2016-05-27 12:14:52.0000000
1002285 2016-05-27 12:14:53.0000000 0026PU009163-031    85.22000    2016-05-27 12:14:53.0000000 2016-07-20 06:44:36.0000000
1002285 2016-07-20 06:44:37.0000000 0026PU009163-031    90.39000    2016-07-20 06:44:37.0000000 2016-07-20 06:44:36.0000000
1002285 2016-11-09 13:37:13.0000000 0026PU009163-031    131.45000   2016-07-20 06:44:37.0000000 9999-12-31 00:00:00.0000000
谢谢，但是这并没有给出正确的结果。TO_DATE应该比下一个FMDT少1秒。 - Rohini Mathur
2
LEAD 在 SQL Server 2008 中不可用。 - Vladimir Baranov
那么，按照.. @VladimirBaranov的要求，保持什么是正确的？ - Chanukya
我不太确定在SQL中是否可能......可以有人分享一下他们的专业知识吗？谢谢。 - Rohini Mathur
是的，在SQL Server中可以实现这个解决方案，它将完美地运行。@RohiniMathur - Chanukya
显示剩余2条评论
1

使用适当的ORDER BY在OUTER APPLY中使用TOP(1)，而不是使用MAX。

此外，您说您想按BINGID、INDUSID、COMP1分组，因此在OUTER APPLY的WHERE子句中使用所有这些列。为什么在您的查询中省略了COMP1？

示例数据

DECLARE @MYTABLE TABLE
(
    BINGID INT,
    INDUSID INT,
    DTSEARCH DATETIME2,
    COMP1 VARCHAR (100),
    LISTPRICE NUMERIC(10,2),
    FROMDT DATETIME2,
    IsRowActive INT
)

INSERT INTO @MYTABLE
SELECT 1002285, 1002, '2016-03-03 04:10:58', '0026PU009163-031',  77.7600, '2015-12-19 12:51:49', 0 UNION ALL
SELECT 1002285, 1002, '2016-05-27 12:14:53', '0026PU009163-031',  85.2200, '2016-05-27 12:14:53', 0 UNION ALL
SELECT 1002285, 1002, '2016-07-20 06:44:37', '0026PU009163-031',  90.3900, '2016-07-20 06:44:37', 0 UNION ALL
SELECT 1002285, 1002, '2016-11-09 13:37:13', '0026PU009163-031', 131.4500, '2016-10-18 13:49:10', 1 UNION ALL
SELECT 1002285, 1002, '2015-12-19 12:51:41', '10122374',          65.1400, '2015-12-19 12:51:41', 0 UNION ALL
SELECT 1002285, 1002, '2016-03-03 04:11:01', '10122374',         117.2100, '2016-03-03 04:11:01', 0 UNION ALL
SELECT 1002285, 1002, '2016-05-27 12:14:45', '10122374',          53.5500, '2016-05-27 12:14:45', 0 UNION ALL
SELECT 1002285, 1002, '2016-07-20 06:44:29', '10122374',          48.5000, '2016-07-20 06:44:29', 0 UNION ALL
SELECT 1002285, 1002, '2016-10-18 13:49:00', '10122374',          75.6800, '2016-10-18 13:49:00', 0 UNION ALL
SELECT 1002285, 1002, '2016-11-09 13:37:02', '10122374',          68.2400, '2016-11-09 13:37:02', 1 UNION ALL
SELECT 1000001, 1002, '2016-03-03 02:22:09', '161GDB1577',        37.1700, '2015-12-18 06:45:05', 0 UNION ALL
SELECT 1000001, 1002, '2016-03-03 02:22:18', '0392347402',        41.9100, '2015-12-18 06:45:14', 0 UNION ALL
SELECT 1000001, 1002, '2016-05-26 14:54:28', '161GDB1577',        46.7100, '2016-05-26 14:54:28', 0 UNION ALL
SELECT 1000001, 1002, '2016-05-26 14:54:42', '0392347402',        54.7100, '2016-05-26 14:54:42', 0 UNION ALL
SELECT 1000001, 1002, '2016-07-15 06:34:33', '161GDB1577',        52.4800, '2016-07-15 06:34:33', 0 UNION ALL
SELECT 1000001, 1002, '2016-07-15 06:34:45', '0392347402',        81.7100, '2016-07-15 06:34:45', 0 UNION ALL
SELECT 1000001, 1002, '2016-10-17 11:26:45', '161GDB1577',        61.6400, '2016-10-17 11:26:45', 0 UNION ALL
SELECT 1000001, 1002, '2016-11-09 02:21:17', '0392347402',        81.9200, '2016-10-17 11:26:58', 1 UNION ALL
SELECT 1000001, 1002, '2016-11-09 02:21:05', '161GDB1577',        78.3500, '2016-11-09 02:21:05', 1 UNION ALL
SELECT 1000005, 1002, '2018-11-09 02:21:05', '556556GHB',         78.3500, '2018-11-09 02:21:05', 1

Query

SELECT
    BINGID,
    INDUSID,
    DTSEARCH,
    COMP1,
    LISTPRICE,
    FROMDT,
    CASE WHEN IsRowActive = 1 THEN '9999-12-31' ELSE oa.TO_DATE END AS TO_DATE,
    IsRowActive
FROM
    @MYTABLE AS mt
    OUTER APPLY 
    (   
        SELECT TOP(1) DATEADD(second, -1, FROMDT) AS TO_DATE
        FROM @MYTABLE AS mt2
        WHERE
            mt2.BINGID = mt.BINGID 
            AND mt2.INDUSID = mt.INDUSID 
            AND mt2.COMP1 = mt.COMP1
            AND mt2.FROMDT > mt.FROMDT
        ORDER BY mt2.FROMDT
    ) AS oa
WHERE
    mt.INDUSID = '1002'
ORDER BY BINGID, INDUSID, COMP1, FROMDT;

Result

+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
| BINGID  | INDUSID |          DTSEARCH           |      COMP1       | LISTPRICE |           FROMDT            |           TO_DATE           | IsRowActive |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
| 1000001 |    1002 | 2016-03-03 02:22:18.0000000 | 0392347402       | 41.91     | 2015-12-18 06:45:14.0000000 | 2016-05-26 14:54:41.0000000 |           0 |
| 1000001 |    1002 | 2016-05-26 14:54:42.0000000 | 0392347402       | 54.71     | 2016-05-26 14:54:42.0000000 | 2016-07-15 06:34:44.0000000 |           0 |
| 1000001 |    1002 | 2016-07-15 06:34:45.0000000 | 0392347402       | 81.71     | 2016-07-15 06:34:45.0000000 | 2016-10-17 11:26:57.0000000 |           0 |
| 1000001 |    1002 | 2016-11-09 02:21:17.0000000 | 0392347402       | 81.92     | 2016-10-17 11:26:58.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1000001 |    1002 | 2016-03-03 02:22:09.0000000 | 161GDB1577       | 37.17     | 2015-12-18 06:45:05.0000000 | 2016-05-26 14:54:27.0000000 |           0 |
| 1000001 |    1002 | 2016-05-26 14:54:28.0000000 | 161GDB1577       | 46.71     | 2016-05-26 14:54:28.0000000 | 2016-07-15 06:34:32.0000000 |           0 |
| 1000001 |    1002 | 2016-07-15 06:34:33.0000000 | 161GDB1577       | 52.48     | 2016-07-15 06:34:33.0000000 | 2016-10-17 11:26:44.0000000 |           0 |
| 1000001 |    1002 | 2016-10-17 11:26:45.0000000 | 161GDB1577       | 61.64     | 2016-10-17 11:26:45.0000000 | 2016-11-09 02:21:04.0000000 |           0 |
| 1000001 |    1002 | 2016-11-09 02:21:05.0000000 | 161GDB1577       | 78.35     | 2016-11-09 02:21:05.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1000005 |    1002 | 2018-11-09 02:21:05.0000000 | 556556GHB        | 78.35     | 2018-11-09 02:21:05.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1002285 |    1002 | 2016-03-03 04:10:58.0000000 | 0026PU009163-031 | 77.76     | 2015-12-19 12:51:49.0000000 | 2016-05-27 12:14:52.0000000 |           0 |
| 1002285 |    1002 | 2016-05-27 12:14:53.0000000 | 0026PU009163-031 | 85.22     | 2016-05-27 12:14:53.0000000 | 2016-07-20 06:44:36.0000000 |           0 |
| 1002285 |    1002 | 2016-07-20 06:44:37.0000000 | 0026PU009163-031 | 90.39     | 2016-07-20 06:44:37.0000000 | 2016-10-18 13:49:09.0000000 |           0 |
| 1002285 |    1002 | 2016-11-09 13:37:13.0000000 | 0026PU009163-031 | 131.45    | 2016-10-18 13:49:10.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1002285 |    1002 | 2015-12-19 12:51:41.0000000 | 10122374         | 65.14     | 2015-12-19 12:51:41.0000000 | 2016-03-03 04:11:00.0000000 |           0 |
| 1002285 |    1002 | 2016-03-03 04:11:01.0000000 | 10122374         | 117.21    | 2016-03-03 04:11:01.0000000 | 2016-05-27 12:14:44.0000000 |           0 |
| 1002285 |    1002 | 2016-05-27 12:14:45.0000000 | 10122374         | 53.55     | 2016-05-27 12:14:45.0000000 | 2016-07-20 06:44:28.0000000 |           0 |
| 1002285 |    1002 | 2016-07-20 06:44:29.0000000 | 10122374         | 48.50     | 2016-07-20 06:44:29.0000000 | 2016-10-18 13:48:59.0000000 |           0 |
| 1002285 |    1002 | 2016-10-18 13:49:00.0000000 | 10122374         | 75.68     | 2016-10-18 13:49:00.0000000 | 2016-11-09 13:37:01.0000000 |           0 |
| 1002285 |    1002 | 2016-11-09 13:37:02.0000000 | 10122374         | 68.24     | 2016-11-09 13:37:02.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
谢谢，但我希望在第三个 TO_DATE 中将值设为“2016-07-20 06:44:36.0000000”，而不是 NULL。 - Rohini Mathur
当没有下一行时，它会从列"IsRowActive = 1"进行计算，因此将在FROMDT列上进行计算，其中TO_DATE = 9999-12-31 00:00:00.0000000。 - Rohini Mathur
@RohiniMathur，如果您想在两行中的FROMDT具有完全相同的值时，在TO_DATE中有一些值，则需要为这些行定义明确的顺序。通常人们除了时间戳之外还使用唯一的行ID，如果时间戳可能重复的话。您的表是否有这样的ID？目前，没有任何一行的FROMDT大于2016-07-20 06:44:37，因此“下一行”未定义。 - Vladimir Baranov
谢谢……弗拉基米尔，你说得完全正确。我正在更改表结构。 - Rohini Mathur
@RohiniMathur，据我所知，我回答中的查询结果与您在更新后的问题中期望的输出相同。行的顺序不同，但值是相同的，所以只需放置任何您需要的ORDER BY即可。为了帮助您自己，将表格按照期望的结果在问题中进行格式化，通过BINGID，INDUSID，COMP1，FROMDT重新排序行，这样就清楚地看到了每个部分的内容，您自己也会明白的。 - Vladimir Baranov
显示剩余2条评论
-1

使用CTE +连接：

终于，解决方案来了。 结果数据最终准确符合您的要求，您还可以使用order by [列名]更改顺序。

代码：

      with cte as  
                ( 
                      SELECT 
                      ROW_NUMBER( ) OVER ( partition by  COMP1 ORDER BY (SELECT 1))
                      rowno,
                      BINGID,
                      INDUSID,    
                      DTSEARCH,   
                      COMP1,  
                      LISTPRICE,  
                      FROMDT,
                      IsRowActive
                      FROM @MYTABLE mt 
                )
                select c1.BINGID,c1.INDUSID, c1.DTSEARCH,  c1.COMP1, c1.LISTPRICE, c1.FROMDT, c1.IsRowActive,
                CASE WHEN c1.IsRowActive = 1 THEN '9999-12-31' ELSE case when ( c2.rowno is null ) THEN '9999-12-31' 
                else DATEADD(second, -1, coalesce(c2.FROMDT,'9999-12-31') ) End END
                AS TO_DATE
                from  cte c1   left join cte c2 
                on c1.rowno+1= c2.rowno and c1.COMP1=c2.COMP1

                order by c1.BINGID,DTSEARCH

同时请查看演示。

抱歉Bhosale先生，这个程序没有给出正确的结果。有两个错误：第一个是表格只有2000条记录，但输出却显示了5000条记录；第二个是对于IsRowActive='0'的情况，它给出了9999-12-31 00:00:00.0000000。 - Rohini Mathur
我能否扩展样本数据与期望输出？ - Rohini Mathur
添加了更多的示例数据。 - Rohini Mathur
这意味着COMP1的值发生了变化。您想根据“COMP1”分别获得结果，并且To_date必须是相应COMP1组的下一个FROMDT，无论sRowActive是“1”还是“0”。这正确吗？ 对于IsRowActive='1'的最后一条记录，应该是什么'To_date'？ - Mr. Bhosale
Bhosale：你说得完全正确。最后一个 To_date 应该是 9999-12-31。 - Rohini Mathur
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接