SQL：组计数在单个结果行中 [英] SQL: Group counts in individual result rows

问题描述

当我运行查询编号1时：

When I run query number 1:

SELECT id, name, COUNT(name) AS count 
  FROM markers 
 WHERE state like 'OR' 
group by named

以下结果：

id  name                count
14  Alsea               2
76  Applegate Valley    1
3   Ashland             9
64  Beaver              1
26  Bend                1
10  Carlton             2

这很好，除了，我希望每个被计数名称的实例都以其各自的ID出现，如查询编号2的结果所示。

This is fine, except, I want each instance of a counted name to appear with its respective ID as seen in the result of query number 2:

SELECT id, name, COUNT(name) AS count 
  FROM markers 
WHERE state like 'OR' 
group by name, id

产生以下结果：

id  name                count
14  Alsea               1
28  Alsea               1
76  Applegate Valley    1
3   Ashland             1
13  Ashland             1
16  Ashland             1
20  Ashland             1
22  Ashland             1
43  Ashland             1
48  Ashland             1
51  Ashland             1
72  Ashland             1
64  Beaver              1
26  Bend                1
10  Carlton             1
27  Carlton             1

我是否可以运行一个查询，该查询将返回查询编号2中的ID和名称以及查询编号1中每行的对应计数，分别为

Is there a query I can run that will return the id and name from query number 2 and the corresponding count from query number 1 in each row as shown below?

id  name                count
14  Alsea               2
28  Alsea               2
76  Applegate Valley    1
3   Ashland             9
13  Ashland             9
16  Ashland             9
20  Ashland             9
22  Ashland             9
43  Ashland             9
48  Ashland             9
51  Ashland             9
72  Ashland             9
64  Beaver              1
26  Bend                1
10  Carlton             2
27  Carlton             2

推荐答案

尝试一下：

SELECT id, name, 
 (SELECT COUNT(name) 
   FROM markers im 
   WHERE state like 'OR' 
   GROUP BY name 
   HAVING im.name=m.name) as count
FROM markers m 
WHERE state like 'OR' 
GROUP BY name, id 

这篇关于SQL：组计数在单个结果行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！