减去两行的值并将其插入新列(不是后续行) [英] subtract values of two rows and inserting it into a new column (not subsequent rows)

问题描述

与我之前的问题相关:如何插入每两行的减法并将其插入新列

我还有一个问题.在下表中；我想计算每个用户最后一次在比赛中获胜所经过的时间.事实上，我应该从当前日期中减去最后一个获胜日期.(place=1) 表示获胜.

I have another question. In the following table; I want to calculate the amount of time that have passed from each user's last winning in a competition. in fact I should subtract the last winning date from current date. (place=1) is indicative of winning.

当前表:

http://www.8pic.ir/images/75206897877200828586.jpg

我想要的结果:

http://www.8pic.ir/images/78832309907063712878.jpg

我针对这个问题编写了以下查询(根据我对上一个问题的回答 此处)！但问题是我得到了很多重复的行！我只有 4000 行，但是当我运行这个查询时，我得到了 40,000 行！问题是什么！你能帮我吗.而且我还为以前没有赢过的用户获得了负值.我希望这些字段为 NULL.

I wrote the following query for this question (according to the answer that I got for my previous question here) ! but the problem is that I get many duplicate rows! I have only 4000 rows, but when I run this query I get 40,000 rows! what is the problem! would you please help me. and also I get negative value for the users that have not won before. I want these fields be NULL.

;WITH [cte15853354] AS
 (
    SELECT 
        [user-name],
        [submissions],
        [date],
        [place],
        [recency],
        ROW_NUMBER() OVER (ORDER BY [user-name], [date] DESC) AS [ROWNUMBER]
    FROM dbo.[top-design1]
 )
SELECT 
    t.[user-name],
    t.[submissions],
    t.[date],
    t.[place],
    t.[recency],
    DATEDIFF(DAY, ISNULL(k.[date],t.[date]),t.[date]) AS [win-recency]
INTO dbo.[top-design2]
FROM [cte15853354] t
LEFT JOIN [cte15853354] k
    ON k.[user-name] = t.[user-name] 
     where k.[place]=1 
    ORDER BY t.[user-name], t.[date] DESC

推荐答案

就这样(实际上不需要案例测试):

There you go (and no need for the case testing, actually):

SELECT [user-name], 
       submissions,
       [date],
       place,
       recency,
       DATEDIFF(DAY, 
       (SELECT TOP(1) [date]
        FROM [top-design1] td1
        WHERE td1.[user-name] = [top-design1].[user-name]
        AND place = 1
        AND [date] < [top-design1].[date]
        ORDER BY [date] DESC), [date]) as recencywin
FROM [top-design1]

我想您可以自己管理以将其用于插入目的.

I suppose you can manage by yourself to use it for insert purposes.

这篇关于减去两行的值并将其插入新列(不是后续行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！