SQL Server SQL只提取表中所有字段的前10条记录，仅按2列分组

mwg9r5ms 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(171)

My SQL skills are too weak to solve this problem, but I am pretty certain it is possible to solve.
To simplify - I have a small table with 5 columns, let's label them A, B, C, D, E. It's 1000 rows.
I need to be able to group by columns A and B where (E is not null and E <> ''). That part I can do.

select T.A, T.B, count(*) as countAll
from TABLE T
where not T.E is null and T.E <> ''
group by T.A, T.B

But then I need to be able to get just the first 10 rows of each group of all the columns ([A-E]) included in each grouping within those parameters. This is where I'm flailing. What I need to see is all the fields in the table returned for the first 10 records of each grouping.
The below seems very similar to what I need but I so far cannot get it to even compile on my end. I must not be using the PARTITION BY clause correctly (never used it before). https://stackoverflow.com/a/51527260/3536926

SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
FROM (
 SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
    ROW_NUMBER () OVER w AS RN
  FROM sometable
  WINDOW w AS (PARTITION BY MemberID ORDER BY ResNumber ASC)
) X
WHERE RN <= 2

Maybe I should be using something besides GROUP BY like PARTITION BY but I'm not familiar with this?

sql-server

来源：https://stackoverflow.com/questions/74720620/sql-to-pull-only-the-first-10-records-of-all-fields-in-table-grouped-by-only-2

2条答案

按热度按时间

ovfsdjhp1#

我想这可能只是一个语法错误。OVER子句的格式如下：

SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate
FROM (
 SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
    ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY ResNumber ASC) AS RN
  FROM sometable
) X
WHERE RN <= 10

赞(0）回复(0）举报 2022-12-10

kmb7vmvb2#

每组返回行的顺序是否重要？这可能有点麻烦，但可能会让您接近您所需的内容。

WITH cte_Stuff
AS (
    SELECT t1.A, t1,B, t1.C, t1.D, t1.E, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowId --Returns a random set unless the ORDER BY is specified
    FROM [TABLE] t1
    INNER JOIN (
        SELECT t2.A, t2.B, COUNT(*) as countAll
        FROM [TABLE] t2
        WHERE ISNULL(t2.E, '') <> ''
        GROUP BY t2.A, t2.B
         ) x ON t1.A = t2.A
                AND t1.B = t2.B
)

SELECT c.A, c.B, c.C, c.D, c.E
FROM cte_Stuff c
WHERE c.RowId <= 10

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server SQL只提取表中所有字段的前10条记录，仅按2列分组

2条答案

相关问题

热门标签

最新问答