SQL Server查询从每个子组中选择1
我有一组数据,需要为每个CON/OWNER/METHOD/MATRIX集合提取一条记录。如果有一个非空RESULT,我想要那个。否则,我想要COUNT最高的那个。我如何查询?SQL Server查询从每个子组中选择1
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
对于结果,我想只是加星标的记录,并且我正在显示每个组是如何分组的。
这是糟糕的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。但是,我没有通过使用更复杂的查询得到更远(例如基于在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?问题)
如何从每个分组中选择一个?
试试这个,不是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
@Siva,你是对的,忘记了 – 2011-06-06 22:43:34
我不知道我们也可以用这种方式编写查询。感谢它解决了我的许多问题。 – Moons 2011-10-19 04:48:21
在支持窗口的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;
注意每个组文本意味着只有一个非空的结果,但你的例子没有。此版本将选择最大结果,除非所有结果相同(例如,NULL),当count
将成为决胜者。顺便说一句,COUNT
作为列名会导致各种悲伤。这个版本在结果和计数都相同的情况下也会选择多行。无论是否有联系,将排名切换到row_number只能得到一个。
噢,我不应该使用COUNT作为名称。我匿名它,所以它没有我们的确切名称。 – thursdaysgeek 2011-06-06 23:24:00
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
)
您可以使用它作为查看和加入。
很难选择接受哪个,@siva,@Paul Creasey或@Andrew Lazarus。所有帮助。安德鲁早期的格式很好,排序也很好; Siva的最后有T1.Ranking预选赛;保罗是第一个让我去的。我对所有人都赞不绝口,既然保罗稍稍领先,我会接受。但都是值得的。 – thursdaysgeek 2011-06-07 23:55:04