MySQL：按值对记录进行计数，然后用其计数更新另一列

Question

三个非常相关的 SQL 问题（我实际上使用的是 mySQL）：

假设一个人员表有两列名称，国家

1）我如何显示有同胞的人？我可以按国家/地区显示公民数量：

select country, count(*) as fellow_citizens 
from people 
group by country

但我似乎无法显示 Fellow_citizens > 的记录。 1.以下内容无效：

select name, country, count(*) as fellow_citizens 
from people 
group by country 
where fellow_citizens > 1;

...并且不会是我想要的，因为我不想将人们分组。

2）为了解决上述问题，我想到将 Fellow_citizens 存储在一个新列中。 这听起来像是诸如 MySQL: Count 等问题的变体来自一个表的记录，然后更新另一个表。不同的是我想更新同一张表。

然而，那里给出的答案在这里不起作用：

update people as dest 
set fellow_citizens = 
    (select count(*) from people as src where src.country = dest.country);

我收到此错误消息：

您无法在 FROM 子句中指定要更新的目标表“dest”

似乎我需要通过另一个临时表来执行此操作。没有临时表可以做到吗？

3）作为上述的变体，如何按国家/地区更新包含人数统计器的列？我发现我可以使用以下内容更新全局计数器：

set @i:=0; update people set counter = @i:=@i+1 order by country;

但是在这里，当国家/地区的值发生变化时，我想重置我的@i计数器。

有没有一种方法可以在 mySQL 中做到这一点，而无需使用成熟的过程代码？

Answer 1

您选择的查询应类似于：

SELECT name, country, count(*) as fellow_citizens  
FROM people  
GROUP BY country  
HAVING fellow_citizens > 1; 

推荐的解决方案
您不想分组，您只是想为每个人重复计数。
这些数据没有标准化，这样的代码并不是一个很好的方法。
但是，以下选择将为您提供所有人员及其计数：

SELECT p.* 
     , s.fellow_citizens
FROM people p
INNER JOIN (
  SELECT country, count(*) as fellow_citizens
  FROM people  
  GROUP BY country  
  HAVING count(*) > 1) s ON (s.country = p.country)

如果您不需要实际计数，只需要来自拥有超过 1 个公民的国家/地区的人员，则编写此查询的另一种方法是（此可能比以前的方法更快，您必须使用您的数据进行测试）：

SELECT p.* 
FROM people p
WHERE EXISTS
      ( SELECT *
        FROM people p2  
        WHERE p2.country = p.country 
          AND p2.PK <> p.PK         -- the Primary Key of the table 
      )

仅在遇到缓慢情况时才执行此操作
如果您想使用更新语句将计数包含到表中，我建议您使用单独的计数表，因为至少这在某种程度上标准化了。

INSERT INTO people_counts (country, pcount)
  SELECT p.country, count(*) as peoplecount
  FROM people p
  GROUP BY p.country
ON DUPLICATE KEY pcount = VALUES(pcount)

然后，您可以将计数表与人员数据连接起来以加快选择速度。

SELECT p.*, c.pcount
FROM people p
INNER JOIN people_counts c ON (p.country = c.country)

Answer 2

1->

select country, count(*) as fellow_citizens 
from people 
group by country 
having fellow_citizens > 1

抱歉，但无法真正理解第2点和第3点。

Answer 3

我会这样做。

create view citizencount as
select country, count(*) citizens 
from people 
group by country 

然后你的查询变成（类似）

select p.personid, p.country, cc.citizens -1 fellow_citizens
from people p
join citizencount cc on
  cc.country = p.country

然后你可能想添加

其中 Fellow_citizens > 0

Answer 4

你有没有尝试过这个来解决你的两个问题

select name, fellow_citizens
from people left join  
(select country, count(*) as fellow_citizens  
 from people group by country  
 having fellow_citizens > 1) as citizens  
 on people.country = citizens.country  

我不太擅长编写sql查询；但我认为这可以解决你的问题