SQL Server 2005 - 分组值，仅显示更改

问题描述：

我正在尝试对一组值进行分组，但我只想在发生更改时显示这些值.

I'm trying to group a set of values, but I only want to display the values if there was a change.

一些示例数据

value | date_loaded | 
  1   |  2012-03-07 |
  1   |  2012-03-06 |
  1   |  2012-03-05 |
  3   |  2012-03-04 |
  4   |  2012-03-03 |
  1   |  2012-03-02 |

所以我想显示最新的值，它是最早的日期，例如

So I would like to display the latest value, with it's earliest date, e.g.

value | date_loaded | 
  1   |  2012-03-05 |
  3   |  2012-03-04 |
  4   |  2012-03-03 |
  1   |  2012-03-02 |

解决这个问题的最佳方法是什么?可以做一个if语句吗?如果值 1 与值 2 不同，则 +1 为更改"?因此，我可以按change1"、change2"等对值进行分组?

What is the best way to go about this? Would it be possible to do an if statement? If value 1 is different to value 2, +1 to "change" ? Therefore I'd be able to group the values by "change1", "change2" etc.?

答

我会使用一个公用表表达式来包含基于 date_loaded 的连续行号，然后在自联接中使用它，如下所示:

I would use a common table expression to include a sequential row number based on date_loaded, and then use that in a self-join, as follows:

CREATE TABLE #temp (
    value INT,
    date_loaded DATETIME
)

INSERT INTO #temp VALUES (1,'2012-03-7')
INSERT INTO #temp VALUES (1,'2012-03-6')
INSERT INTO #temp VALUES (1,'2012-03-5')
INSERT INTO #temp VALUES (3,'2012-03-4')
INSERT INTO #temp VALUES (4,'2012-03-3')
INSERT INTO #temp VALUES (1,'2012-03-2')

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY date_loaded) AS n, value, date_loaded FROM #temp)
SELECT t2.value, t2.date_loaded
FROM cte t2 LEFT JOIN cte t1 ON t2.n = t1.n + 1
WHERE t2.value <> ISNULL(t1.value, -1)

DROP TABLE #temp

SQL Server 2005 - 分组值，仅显示更改

相关推荐