将排名添加到每个组的第一行

This is returning what I want but is there a simpler, more elegant, approach?

这是我想要的,但是有更简单,更优雅的方法吗?

IF OBJECT_ID('TEMPDB..#test') IS NOT NULL DROP TABLE #test;
CREATE TABLE #test
(
    userAcc VARCHAR(100),
    game VARCHAR(100),
    amount INT
);

INSERT INTO #test
values
    ('jas', 'x', 10),
    ('jas', 'y', 100),
    ('jas', 'z', 20),
    ('sam', 'j', 10),
    ('sam', 'q', 5);



--initial table sample
SELECT  userAcc,
        game,
        amount 
FROM    #test;


WITH 
X AS
(
    SELECT  rn = ROW_NUMBER() OVER (PARTITION BY userAcc ORDER BY game),
            userAcc,
            game,
            amount, 
            rk = RANK() OVER (PARTITION BY userAcc ORDER BY amount DESC)
    FROM    #test
),
Y AS
(
    SELECT  RK,userAcc,
            game,
            targ = rn
    FROM    X
    WHERE   rk = 1
)
SELECT  X.userAcc,
        X.game,
        X.amount,
        ISNULL(Y.targ,0) 
FROM    X 
        LEFT OUTER JOIN Y
        ON
        X.userAcc = Y.userAcc AND
        X.rn = Y.rk
ORDER BY X.userAcc,X.rn;

It returns this:

它返回:

Here is the initial table:

这是初始表:

What the script is doing is this:

脚本的作用是:

Add a new column to original table

将新列添加到原始表

In new column add the rank of the game for each userAcc with the highest amount.

在新列中,为每个具有最高金额的用户添加游戏等级。

The rank is the alphabetical position of the game with the highest amount amongst the user's games. So for jas his highest game is y and that is positioned 2nd amongst his games.

排名是用户游戏中具有最高金额的游戏的字母位置。所以对于jas来说,他的最高比赛是y,并且在他的比赛中排名第二。

The rank found in step 3 should only go against the first alphabetical game of the respective user.

在步骤3中找到的等级应该仅对应于相应用户的第一个字母游戏。

1 个解决方案

#1

You don't need a join for this. You can use accumulation.

你不需要加入。你可以使用积累。

If I understand correctly:

如果我理解正确:

 select userAcc, game, amount,
        isnull( (case when rn = 1
               then max(case when rk = 1 then rn end) over (partition by userAcc)
           end),0) as newcol
  from (select t.*,
               ROW_NUMBER() OVER (PARTITION BY userAcc ORDER BY game) as rn,
               RANK() OVER (PARTITION BY userAcc ORDER BY amount DESC) as rk
        from #test t
       ) t
  order by userAcc;

#1