英文:
Using T-SQL, I am creating a calculated column and I want to retrieve the value of the previous row of that column - is it possible?
问题
我有一个要求，需要对一些数据进行分组填充。
DDL脚本：
CREATE TABLE #TEMP
(
OTHERCOL VARCHAR(10),
RANK_NO INT,
TAG INT
)
INSERT INTO #TEMP VALUES ('OC1',1,0)
INSERT INTO #TEMP VALUES ('OC1',2,0)
INSERT INTO #TEMP VALUES ('OC1',3,0)
INSERT INTO #TEMP VALUES ('OC2',1,0)
INSERT INTO #TEMP VALUES ('OC2',2,0)
INSERT INTO #TEMP VALUES ('OC2',3,9999)
INSERT INTO #TEMP VALUES ('OC2',4,0)
INSERT INTO #TEMP VALUES ('OC2',5,0)
INSERT INTO #TEMP VALUES ('OC3',1,0)
INSERT INTO #TEMP VALUES ('OC3',2,0)
INSERT INTO #TEMP VALUES ('OC3',3,0)
使用DENSE_RANK在OTHERCOL上，我得到了如下所示的FINAL_TAG：
| OTHERCOL | RANK_NO | TAG | FINAL_TAG |
|---|---|---|---|
| OC1 | 1 | 0 | 1 |
| OC1 | 2 | 0 | 1 |
| OC1 | 3 | 0 | 1 |
| OC2 | 1 | 0 | 2 |
| OC2 | 2 | 0 | 2 |
| OC2 | 3 | 9999 | 2 |
| OC2 | 4 | 0 | 2 |
| OC2 | 5 | 0 | 2 |
| OC3 | 1 | 0 | 3 |
| OC3 | 2 | 0 | 3 |
| OC3 | 3 | 0 | 3 |
然而，我希望结果如下所示的期望值。我可以在Excel中实现这个目标，但在SQL Server中无法做到。
我尝试使用SELECT CASE，但它不起作用，因为值被重置了... 有没有办法更新变量的值并根据条件使用它？
英文:
I have a requirement to populate some data with some grouping.
DDL Script:
CREATE TABLE #TEMP
(
OTHERCOL VARCHAR(10),
RANK_NO INT,
TAG INT
)
INSERT INTO #TEMP VALUES ('OC1',1,0)
INSERT INTO #TEMP VALUES ('OC1',2,0)
INSERT INTO #TEMP VALUES ('OC1',3,0)
INSERT INTO #TEMP VALUES ('OC2',1,0)
INSERT INTO #TEMP VALUES ('OC2',2,0)
INSERT INTO #TEMP VALUES ('OC2',3,9999)
INSERT INTO #TEMP VALUES ('OC2',4,0)
INSERT INTO #TEMP VALUES ('OC2',5,0)
INSERT INTO #TEMP VALUES ('OC3',1,0)
INSERT INTO #TEMP VALUES ('OC3',2,0)
INSERT INTO #TEMP VALUES ('OC3',3,0)
Using DENSE_RANK over OTHERCOL, I have got FINAL_TAG as shown here:
| OTHERCOL | RANK_NO | TAG | FINAL_TAG |
|---|---|---|---|
| OC1 | 1 | 0 | 1 |
| OC1 | 2 | 0 | 1 |
| OC1 | 3 | 0 | 1 |
| OC2 | 1 | 0 | 2 |
| OC2 | 2 | 0 | 2 |
| OC2 | 3 | 9999 | 2 |
| OC2 | 4 | 0 | 2 |
| OC2 | 5 | 0 | 2 |
| OC3 | 1 | 0 | 3 |
| OC3 | 2 | 0 | 3 |
| OC3 | 3 | 0 | 3 |
However, I want the result to be as shown below in the desired value. I could achieve this in Excel, but I'm unable to so in SQL Server.
Details of the activity done is attached here
I have tried to use SELECT CASE but it is not working as the value is getting reset... Is there any way to so that we can update the values of variable and use the same based on the condition?
答案1
得分: 1
你可以通过使用分析函数 sum() 和 case 子句来应用条件累计运算来获得所需的值：
select *, DENSE_RANK() over (order by OTHERCOL) as FINAL_TAG,
DENSE_RANK() over (order by OTHERCOL)
+ sum(case when TAG > 0 then 1 else 0 end) over(order by OTHERCOL, RANK_NO ROWS UNBOUNDED PRECEDING) as DESIRED_VALUE
from #TEMP
英文:
You can get the desired value by applying a conditional running total using analytic function sum() and case clause :
select *, DENSE_RANK() over (order by OTHERCOL) as FINAL_TAG,
DENSE_RANK() over (order by OTHERCOL)
+ sum(case when TAG > 0 then 1 else 0 end) over(order by OTHERCOL, RANK_NO ROWS UNBOUNDED PRECEDING) as DESIRED_VALUE
from #TEMP
答案2
得分: 0
你可以首先对Tag进行条件计数，排除（通过置空）Tag = 0的情况。
然后，使用该列作为另一个排序列进行DENSE_RANK。
SELECT *,
DENSE_RANK() OVER (ORDER BY OTHERCOL, c)
FROM (
SELECT *,
c = COUNT(NULLIF(TAG, 0)) OVER (PARTITION BY OTHERCOL ORDER BY RANK_NO)
FROM #TEMP t
) t;
英文:
You can do first a conditional count of Tag, excluding (by nulling out) the cases where Tag = 0.
Then do DENSE_RANK using that column as another ordering column.
SELECT *,
DENSE_RANK() OVER (ORDER BY OTHERCOL, c)
FROM (
SELECT *,
c = COUNT(NULLIF(TAG, 0)) OVER (PARTITION BY OTHERCOL ORDER BY RANK_NO)
FROM #TEMP t
) t;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论