数据操作 Row_Number() SQL SERVER [英] Data Manipulation Row_Number() SQL SERVER

问题描述

我需要一些指导和帮助来解决 SQL SERVER 2012 中的数据操作问题.

I need some guidance and help with a data manipulation question in SQL SERVER 2012.

这是我的数据的样子:

=================================================
YearMonth LocationCode  New     ValidTo
=================================================
201412         2020     1       201502
201501        2020      1       201503
201503        3030      1       201506
201506        3030      1       201509

问题描述

如果你看上面的表格，你会看到列 YearMonth , locationCode, New 告诉你 locationCode 是该行月份的新内容.ValidTo 列显示它对哪个 ValidTo YearMonth 有效.

if you look at the above table, you will see the column YearMonth , locationCode, New which tells whether the locationCode is new for the month in the row. The ValidTo column shows to which ValidTo YearMonth it is validto.

例如，对于 YearMonth 201412，locationCode 2020 是 1，这意味着这里是 New 和 LocationCode 被认为是新的，直到 ValidTo 201502.

For the example, for YearMonth 201412, the locationCode 2020 is 1, which means here it is New and that LocationCode is considered New till ValidTo 201502.

我的问题是，我需要将出现在最早的 YearMonth 列中的每个 LocationCode 设置为 New，在 ValidTo YearMonth 之前，这种情况是1".

My problem is that I need to make each LocationCode , that appears in the earliest YearMonth Column, to New, is this case "1" till the ValidTo YearMonth.

目标:

=================================================
YearMonth LocationCode  New     ValidTo
=================================================
201412         2020     1       201502
201501        2020      1       201503
201503        3030      1       201506
201506        3030      0       201509

基本上，我需要找出每个 LocationCode 的 MIN()，然后将其归类为 NEW,"1" 并且如果LocationCode 出现在 MIN() YearMonth 和 ValidTo 中，然后在 New 中对 LocationCode 进行分类代码>为1".

Basically, I need to find out the MIN() for each LocationCode and then categorize it as NEW,"1" and if the LocationCode appears within the MIN() YearMonth and ValidTo then categorize the LocationCode in New as "1".

我该怎么做?上表提供了一个直观的示例.

How Can I do that? The above table provides a visual example.

我编辑了我的决赛桌，以便更容易理解我的问题.

I have edited my final table to make it simpler to understand my question.

基本上，LocationCode 3030 的 MIN() Year 是 201503，LocationCode 的有效期至 201506，如 中所示Validto 列.如果 LocationCode 3030 出现在 YearMonth 行，201505 和 VALIDTO 到 201506，那么我们将其归类为 New(1) 也是.

Basically,the MIN() Year for LocationCode 3030 is 201503 and the LocationCode is valid till 201506 as demostrated in the Validto Column. If the LocationCode 3030 were to appear in the YearMonth row, 201505 with a VALIDTO till 201506, then we classify it as New(1) as well.

基本上是伪代码

SELECT

MIN(YearMonth),
LocationCode

from Tabl

如果 LocationCode 在 MIN(YearMonth) AND ValidTO 时间段内，则将其归类为 1.我该怎么做?

If LocationCode , is in MIN(YearMonth) AND ValidTO timeperiod then classify it as 1. How can I do this?

推荐答案

请尝试以下查询来更新表:最里面的查询计算每个 LocationCode 的最小 Yearmonth 用于计算所有需要的 ValidTo 和 LocationCode在 ValidTo 列

Please try the below query for updating the table: The inner most query calculates the minimum Yearmonth per LocationCode which is used to calculate all ValidTo and LocationCodes which need to be updated to 1 in ValidTo column

update t2
set New= CASE when t1.ValidTo is null then 0 else 1 end 
FROM tbl t2
LEFT JOIN 
(
  select t.ValidTo, t.locationCode 
       from tbl t inner join
           (
             select 
                MIN(YearMonth) as MYM, 
                LocationCode 
             from tbl
               group by LocationCode
            ) g 
       on t.YearMonth=g.MYM and t.LocationCode=g.LocationCode
) t1
on t2.ValidTo=t1.ValidTo and t2.LocationCode=t1.LocationCode

用于演示的示例 sql 小提琴:http://sqlfiddle.com/#!6/229d8

sample sql fiddle for demo: http://sqlfiddle.com/#!6/229d8

这篇关于数据操作 Row_Number() SQL SERVER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！