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

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集提取一条记录。如果存在非空结果,我想要那个。否则,我想要COUNT最高的那个。我该如何查询?

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.

为了一个结果,我想只是星号记录,我正在展示如何分组。

This is bad SQL:

这是不好的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

...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?)

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

How do I select one from each grouping?

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

3 个解决方案

#1

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

试试这个,不是100%确定语法是正确的,但它很接近。

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

#2

In an RDBMS that supports windowing (includes SQL Server 2008, I believe)

在支持窗口的RDBMS中(包括SQL Server 2008,我相信)

SELECT CON, OWNER, METHOD, MATRIX, RESULT, `COUNT`
FROM
 (SELECT CON, OWNER, METHOD, MATRIX, RESULT, `COUNT`, 
  RANK() OVER (PARTITION BY CON, OWNER, METHOD, MATRIX
               ORDER BY RESULT DESC, `COUNT` DESC) AS rk
  FROM temptable
 ) AS s
WHERE rk=1;

Note that your text implies only one not-null result per group but your example doesn't. This version will select the maximum result unless all results are the same (e.g., NULL) when count will be the tiebreaker. BTW, COUNT as a column name is going to cause all sorts of grief. This version also selects multiple rows in case of ties in both result and count. Switch rank to row_number to get only one regardless of whether there is a tie.

请注意,您的文本仅暗示每个组只有一个非空结果,但您的示例却没有。此版本将选择最大结果,除非所有结果相同(例如,NULL),当count将成为决胜局时。顺便说一句,COUNT作为列名会引起各种各样的悲痛。如果结果和计数都有关联,此版本也会选择多行。无论是否存在平局,都将排名切换为row_number以获得一个。

#3

select DetailID, field2, field3, field4, MasterID 
from table outer
where DetailID= 
    (
        select max(DetailID) 
        From table inner 
        where outer.MasterID = inner.MasterID
        group by MasterID 
    )

You can use it as view and join.

您可以将其用作视图和加入。

#1