如何按所有列统计行数并返回列名与行数？求多列数据表汇总方案

Hey there! Let's break down your two SQL needs step by step.

I assume you're looking to count the number of non-null rows for each column (since counting total table rows per column would just return the same number for every column, which isn't very useful). Here's how to do it, whether you have a few columns or a ton:

For a small number of columns (static SQL) #

If you know your column names upfront, you can use UNION ALL to stack results:

SELECT 
    'Column 1' AS ColumnName, COUNT(`Column 1`) AS RowCount
UNION ALL
SELECT 
    'Column 2' AS ColumnName, COUNT(`Column 2`) AS RowCount
UNION ALL
SELECT 
    'Column 3' AS ColumnName, COUNT(`Column 3`) AS RowCount;

This will return a two-column result where each row lists a column name and how many non-null values it has.

For a table with tons of columns (dynamic SQL) #

Manually writing every column is a pain, so we can auto-generate the query using your database's system catalog. Here are examples for common databases:

MySQL/MariaDB

SET @sql = NULL;
SELECT GROUP_CONCAT(
    CONCAT("SELECT '", column_name, "' AS ColumnName, COUNT(", column_name, ") AS RowCount FROM your_table")
    SEPARATOR " UNION ALL "
) INTO @sql
FROM information_schema.columns
WHERE table_schema = 'your_database' AND table_name = 'your_table';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL

DO $$
DECLARE
    col_record record;
    query_text text := '';
BEGIN
    FOR col_record IN 
        SELECT column_name FROM information_schema.columns 
        WHERE table_schema = 'public' AND table_name = 'your_table'
    LOOP
        query_text := query_text || 'SELECT ''' || col_record.column_name || ''' AS ColumnName, COUNT(' || col_record.column_name || ') AS RowCount FROM your_table UNION ALL ';
    END LOOP;
    -- Remove the trailing " UNION ALL "
    query_text := LEFT(query_text, LENGTH(query_text) - 10);
    EXECUTE query_text;
END $$;

SQL Server

DECLARE @sql NVARCHAR(MAX);
SET @sql = (
    SELECT STRING_AGG(
        CONCAT(N'SELECT ''', COLUMN_NAME, ''' AS ColumnName, COUNT(', COLUMN_NAME, ') AS RowCount FROM your_table'),
        N' UNION ALL '
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'your_table'
);

EXEC sp_executesql @sql;

Just replace your_database, your_table, and adjust schema names (like public or dbo) to match your setup.

From your example, it looks like you want a clean two-column result where each row maps a table column to its count (whether that's non-null rows, distinct values, etc.). This is actually the same output as the solution above—you just need to tweak the count logic if you want something different:

If you want distinct value counts instead of non-null rows #

Just swap COUNT(column_name) with COUNT(DISTINCT column_name) in any of the queries above. For example, the static SQL version becomes:

SELECT 
    'Column 1' AS Column, COUNT(DISTINCT `Column 1`) AS Count
UNION ALL
SELECT 
    'Column 2' AS Column, COUNT(DISTINCT `Column 2`) AS Count
UNION ALL
SELECT 
    'Column 3' AS Column, COUNT(DISTINCT `Column 3`) AS Count;

If you meant grouping by each column individually (and combining results) #

If your original thought was to get the count of each distinct value for every column (e.g., "Column 1 has value X 24 times, value Y 10 times"), here's how to do that dynamically with MySQL:

SET @sql = NULL;
SELECT GROUP_CONCAT(
    CONCAT("SELECT '", column_name, "' AS ColumnName, ", column_name, " AS Value, COUNT(*) AS Count FROM your_table GROUP BY ", column_name)
    SEPARATOR " UNION ALL "
) INTO @sql
FROM information_schema.columns
WHERE table_schema = 'your_database' AND table_name = 'your_table';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This will return rows like:

Just adjust the dynamic SQL logic for your database if you're not using MySQL.

内容的提问来源于stack exchange，提问作者MANY