SQL Server 查询从每个子组中选择 1

2021-09-10 00:00:00 tsql sql-server-2008 sql-server

我有一组数据，需要为每个 CON/OWNER/METHOD/MATRIX 集提取一条记录.如果有一个非空的结果，我想要那个.否则，我想要 COUNT 最高的那个.我如何查询?

I have a set of data and need to pull out one record for each CON / OWNER / METHOD / MATRIX set. If there is a non-null RESULT, I want that one. Otherwise, I want the one with the highest COUNT. How do I query this?

CON      OWNER      METHOD      MATRIX  RESULT  COUNT
*CON_1   OWNER_1    METHOD_A    SOLID   NULL    503
CON_1    OWNER_1    METHOD_A    SOLID   NULL    1

*CON_1   OWNER_1    METHOD_A    SOIL    NULL    1305
CON_1    OWNER_1    METHOD_A    SOIL    NULL    699

*CON_2   OWNER_2    METHOD_B    SOLID   290     687
CON_2    OWNER_2    METHOD_B    SOLID   NULL    NULL
CON_2    OWNER_2    METHOD_B    SOLID   450     600

CON_2    OWNER_2    METHOD_B    WATER   NULL    1
*CON_2   OWNER_2    METHOD_B    WATER   400     NULL

结果，我只想要加星标的记录，并且我正在展示每个集合是如何分组的.

for a result, I would like just the starred records, and I'm showing how each set is grouped.

这是糟糕的 SQL:

select top (1) CON, OWNER, METHOD, MATRIX, RESULT, COUNT
from #TempTable
group by CON, OWNER, METHOD, MATRIX
order by CON, OWNER, METHOD, MATRIX, COUNT

...因为我的计数不是聚合函数的一部分.它也不处理 RESULT 是否为 NULL，并且 top (1) 不会从每个分组返回 1.但是，我还没有通过使用更复杂的查询(例如基于 如何从子查询(在 SQL Server 中)中选择多个列，这些列应该为主中的每条记录有一个记录(选择前 1 个)查询?)

...because my count isn't part of the aggregate function. Nor does it deal with the RESULT being NULL or not, and top (1) won't return 1 from each grouping. However, I've not got farther by using a more complex query (such as based on the question at How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?)

如何从每个分组中选择一个?

How do I select one from each grouping?

推荐答案

试试这个，虽然不是 100% 确定语法正确，但很接近.

Try this, not 100% sure the syntax is right, but it is close.

select 
    * 
from
    (select
        CON,
        OWNER,
        METHOD,
        MATRIX,
        RESULT,
        COUNT,
        RANK() OVER(PARTITION BY CON, OWNER, METHOD,MATRIX ORDER BY RESULT,COUNT DESC) as rnk
    FROM #TempTable
) a
WHERE rnk = 1

相关文章