获取同一姓氏对应各名字出现次数的SQL查询实现

First, let's start with the foundational query that gives you the core counts you need. This groups records by both last name and first name, then counts how many times each combination appears:

SELECT 
  last_name, 
  first_name, 
  COUNT(*) AS occurrence_count
FROM 
  your_table_name
GROUP BY 
  last_name, 
  first_name
ORDER BY 
  last_name, 
  occurrence_count DESC;

This returns a structured result set like:

If you want to aggregate these into a single line per last name (matching your example formats), you'll need string aggregation functions—these vary by database system. Here are examples for the most common ones:

MySQL/MariaDB #

Use GROUP_CONCAT to combine first name and count pairs:

SELECT 
  last_name,
  GROUP_CONCAT(CONCAT(first_name, ' (', occurrence_count, ')') SEPARATOR ', ') AS name_counts
FROM (
  SELECT 
    last_name, 
    first_name, 
    COUNT(*) AS occurrence_count
  FROM 
    your_table_name
  GROUP BY 
    last_name, 
    first_name
) AS grouped_counts
GROUP BY 
  last_name
ORDER BY 
  last_name;

Output:

Smith: John (3), David (1), Jane (1)
Black: Jack (2), Jane (1), Samantha (1)

PostgreSQL #

Use STRING_AGG for string aggregation:

SELECT 
  last_name,
  STRING_AGG(CONCAT(first_name, ' (', occurrence_count, ')'), ', ') AS name_counts
FROM (
  SELECT 
    last_name, 
    first_name, 
    COUNT(*) AS occurrence_count
  FROM 
    your_table_name
  GROUP BY 
    last_name, 
    first_name
) AS grouped_counts
GROUP BY 
  last_name
ORDER BY 
  last_name;

SQL Server (2017+) #

STRING_AGG is available here too:

SELECT 
  last_name,
  STRING_AGG(CONCAT(first_name, ' (', occurrence_count, ')'), ', ') AS name_counts
FROM (
  SELECT 
    last_name, 
    first_name, 
    COUNT(*) AS occurrence_count
  FROM 
    your_table_name
  GROUP BY 
    last_name, 
    first_name
) AS grouped_counts
GROUP BY 
  last_name
ORDER BY 
  last_name;

Oracle (11g+) #

Use LISTAGG with optional ordering:

SELECT 
  last_name,
  LISTAGG(CONCAT(first_name, ' (', occurrence_count, ')'), ', ') WITHIN GROUP (ORDER BY occurrence_count DESC) AS name_counts
FROM (
  SELECT 
    last_name, 
    first_name, 
    COUNT(*) AS occurrence_count
  FROM 
    your_table_name
  GROUP BY 
    last_name, 
    first_name
) AS grouped_counts
GROUP BY 
  last_name
ORDER BY 
  last_name;

Key Notes: #

• Replace your_table_name with your actual table name.
• The subquery calculates individual counts per last+first name pair, then the outer query aggregates those into a single string per last name.
• Adjust the concatenation logic if you want the comma-separated format without parentheses (like your first example). For instance, in MySQL:

  SELECT 
    last_name,
    GROUP_CONCAT(CONCAT(first_name, ', ', occurrence_count) SEPARATOR ', ') AS name_counts
  FROM (/* same subquery as above */) AS grouped_counts
  GROUP BY last_name;
  

Content of this question originates from Stack Exchange, asked by Philip Trauring