给每个组的第一行添加一个等级

这正在返回我想要的，但有没有更简单，更优雅的方法？

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;

它返回这个：

这是最初的表格：

脚本正在做的是这样的：

向原始表中添加一个新列

在新栏中添加最高金额的每个userAcc的游戏等级。

等级是用户游戏中具有最高金额的游戏的字母位置。 所以对于jas而言，他的最高分是y，并且在他的比赛中位列第二。

在第3步中找到的排名只应该违背相应用户的第一个字母游戏。

---

你不需要为此join 。 你可以使用积累。

如果我理解正确：

 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;

链接地址: http://www.djcxy.com/p/90783.html

上一篇: Adding a rank to first row of each group

下一篇: Get values by property name from an object at different levels