比如,随着增加记录或修改记录,如果A对应的col2的值变化,就标记为1记录过程如下 col1 col2 col3 col4 A 1 2014-1-1 A 2 2014-1-1 1 A 1 2014-1-1 1 A 1 2014-1-2 A 6 2014-1-3 1
update 表1 set col2='?',col3='?',col4=(case when @value=col2 then 1 else '' end) where condition关键是用好Case when
根据日期查询,如果和查询日期相邻的上一条记录比较,如果col2变化,就col4=1
WITH CTE AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col3)RN FROM 表1 ) SELECT T1.*,CASE WHEN T1.col2<>T2.col2 THEN 1 END[col3] FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN=T2.RN+1
col1 col2 col3 col4
A 1 2014-1-1
A 2 2014-1-1 1
A 1 2014-1-1 1
A 1 2014-1-2
A 6 2014-1-3 1
where condition关键是用好Case when
SELECT *,ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col3)RN FROM 表1
)
SELECT T1.*,CASE WHEN T1.col2<>T2.col2 THEN 1 END[col3]
FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN=T2.RN+1