多列分组的SQL计数查询

Question

我有一个包含三个填充列的表格，分别命名为“姓名”、“城市”和“职业”。 我想在同一张表格中创建一个新的列，其中包含具有相同职业的人数。

"Name" | "City" | "Occupation"
------------------------------
Amy    | Berlin | Plumber
Bob    | Berlin | Plumber
Carol  | Berlin | Lawyer
David  | London | Plumber

我想要一个包含以下内容的表格：

"Name" | "City" | "Occupation" | "Number"
---------------------------------------
Amy    | Berlin | Plumber      | 2
Bob    | Berlin | Plumber      | 2
Carol  | Berlin | Lawyer       | 1
David  | London | Plumber      | 1

创建新列的SQL查询语句应该是什么样子的？我想实际在数据库中创建一个新列，以便稍后访问。

- Christian

---

我读了标题，想知道为什么你的查询在摸索... 真的！ - thecoshman

在您的表格中，行是水平的，列是垂直的。例如，“Amy - Berlin - 水管工”是一行，而“姓名”，“城市”和“职业”是列。 - KM.

Answer 1

select tbl.name, tbl.city, tbl.occupation,  x.number
from tbl
join
(
    select occupation, count(*) as number
    from tbl
    group by occupation
) as x on x.occupation = tbl.occupation

- Michael Buen

Answer 2

简单的自连接：

SELECT t0.Name, t0.City, t0.Occupation, COUNT(*) AS Number
FROM sometable AS t0
JOIN sometable AS t1 ON t1.Occupation=t0.Occupation
GROUP BY t0.Name, t0.City, t0.Occupation

如果Name是一个主键，你可以只按照它来进行分组，因为其他列会对其有函数依赖性。当然，Name通常不是一个很好的主键。

（如果你想要除总数以外的其他人数，那么可能需要COUNT(*)-1。不过现在不太清楚；你例子中的数字也无论如何都不对应。）

如果你必须更改架构（我不建议这种去规范化的做法，除非你绝对确定你需要它；请参阅评论），你可以使用UPDATE JOIN和Michael的子查询来完成。

ALTER TABLE sometable ADD COLUMN Number INTEGER NOT NULL;
UPDATE sometable AS t0 JOIN (
    SELECT Occupation, COUNT(*) AS Number
    FROM sometable
    GROUP BY Occupation
) AS t1 ON t1.Occupation=t0.Occupation
SET t0.Number= t1.Number;

- bobince

---

谢谢，不过我希望将数字写入行中，因为每次需要数字时进行连接可能会太昂贵。 - Christian

过早的优化：在必要之前不要更改模式。使用Occupation索引，这将非常快；我可以在约5秒钟内对500k帖子的Web论坛数据库执行类似的自连接和计数查询。Michael的查询甚至更快（虽然对于有限的结果，它会更慢）；哪个更好取决于您的数据库中具体内容，但是只要有适当的索引，两者都将很快。 - bobince

我在我的数据库中有2000万条记录，而我需要进行成千上万次的查询，所以我需要高性能。 - Christian