問(wèn)題描述

我有一組數(shù)據(jù)，需要為每個(gè) CON/OWNER/METHOD/MATRIX 集提取一條記錄.如果有一個(gè)非空的結(jié)果，我想要那個(gè).否則，我想要 COUNT 最高的那個(gè).我如何查詢?

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

結(jié)果，我只想要加星標(biāo)的記錄，并且我正在展示每個(gè)集合是如何分組的.

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

...因?yàn)槲业挠?jì)數(shù)不是聚合函數(shù)的一部分.它也不處理 RESULT 是否為 NULL，并且 top (1) 不會(huì)從每個(gè)分組返回 1.但是，我還沒(méi)有通過(guò)使用更復(fù)雜的查詢(例如基于 如何從子查詢(在 SQL Server 中)中選擇多個(gè)列，這些列應(yīng)該為主中的每條記錄有一個(gè)記錄(選擇前 1 個(gè))查詢?)

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

如何從每個(gè)分組中選擇一個(gè)?

How do I select one from each grouping?

推薦答案

試試這個(gè)，雖然不是 100% 確定語(yǔ)法正確，但很接近.

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

這篇關(guān)于SQL Server 查詢從每個(gè)子組中選擇 1的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！