sql server 分组

分组筛选第一条

    SELECT * from(
        SELECT
        *, row_number () OVER (
            partition BY [分组列]
            ORDER BY [排序列] DESC
        ) rowid
        FROM [table]
        WHERE 1=1
    ) t
    WHERE rowid = 1;

分组后删除多余的，保留第一条

    delete [table] where 主键 in(
        SELECT 主键 from(
            SELECT
            *, row_number () OVER (
                partition BY [分组列]
                ORDER BY [排序列] DESC
            ) rowid
            FROM [table]
            WHERE 1=1
        ) t
        WHERE rowid >1
    )

 

posted @ 2022-01-13 15:29  yyutudou  阅读(143)  评论(0)  编辑  收藏  举报