我是SQL Server的新手,所以我现在很抱歉。
我有一个表,我想按field1
分组,并返回在counted
字段中具有最高关联计数的field2
记录。我通常会在MS ACCESS中用两个查询来完成这项工作,第一个查询按降序返回数据,第二个查询使用First()函数选择第一条记录,如下所示:-
查询1
SELECT t.field1, t.field2, Count(t.counted) AS count1
FROM Table1 AS t
WHERE (((t.counted)=2))
GROUP BY t.field1, t.field2
ORDER BY t.field1, Count(t.counted) DESC;
查询2(基于上述查询1)
SELECT q.field1, First(q.field2) AS firstoffield2
FROM q
GROUP BY q.field1;
SOURCE数据和查询结果我正在寻找
在SQL Server 2008查询中,我很难获得与上面相同的结果。有人能帮忙吗?(请提供我需要使用的精确SQL)。
以下是数据的子集和结果的示例:-
表1
field1 ¦ field2 ¦ counted
10 ¦ 20 ¦ 2
10 ¦ 30 ¦ 2
10 ¦ 20 ¦ 2
20 ¦ 30 ¦ 0
20 ¦ 40 ¦ 0
20 ¦ 50 ¦ 1
20 ¦ 50 ¦ 2
20 ¦ 60 ¦ 1
Query1结果(按字段1分组,计数,其中"计数"字段记录为"2")
field1 ¦ field2 ¦ count1
10 ¦ 20 ¦ 2
10 ¦ 30 ¦ 1
20 ¦ 50 ¦ 1
查询2结果(我想从SQL获得的输出)
field1 ¦ firstoffield2
10 ¦ 20
20 ¦ 50
我希望这能有所帮助,谢谢大家。
WITH
q AS
(
Put your query one here
)
,
sequenced AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY count1 DESC) AS sequence_id,
*
FROM
q
)
SELECT
*
FROM
sequenced
WHERE
sequence_id = 1
要将其更改为LAST(),请更改ROW_NUMBER()函数中的顺序方向。
这不是我写过的最优雅的查询,但像这样的查询怎么样:
SELECT qSource.Field1, qSource.Field2
FROM (SELECT Field1, Field2, COUNT(Counted) AS Count1
FROM dbo.Table1
WHERE Counted = 2
GROUP BY Field1, Field2)qSource
INNER JOIN (SELECT q.Field1,MAX(q.Count1) AS HighestCount
FROM (SELECT Field1, Field2, COUNT(Counted) AS Count1
FROM dbo.Table1
WHERE Counted = 2
GROUP BY Field1, Field2) q
GROUP BY q.Field1) qHighest
ON qSource.Field1 = qHighest.Field1
AND qSource.Count1 = qHighest.HighestCount
ORDER BY qSource.Field1
我一直在寻找同样的东西,不喜欢复杂的解决方案,所以我一直在找,发现了这个:
https://www.sqlservercentral.com/forums/topic/t-sql-equivalent-to-ms-access-first-function
使用";顶部1";
选择前1名SomeColumn来自SomeTable按订购
这真的很好,因为你添加了一个复杂的ORDERBY功能,可以准确地得到你想要的。如果有多个第一