SQL查询如何排除无数据或值为0的列？

Hey there! Great question—yes, you absolutely can achieve this, but it requires using dynamic SQL. Static SQL can't adjust the columns it returns based on runtime data (like which columns have non-zero values in a specific row), so dynamic SQL is the way to go here.

Example Implementation (SQL Server) #

This script will automatically detect which columns have non-zero values for the player 'cagri', then build and run a SELECT statement that only returns those columns. It works for all numeric columns in your table, so you don't have to manually list all 30 stats:

DECLARE @TargetPlayer VARCHAR(50) = 'cagri'
DECLARE @AllNumericColumns NVARCHAR(MAX) = ''
DECLARE @NonZeroColumns NVARCHAR(MAX) = ''
DECLARE @UnpivotQuery NVARCHAR(MAX)
DECLARE @FinalSelectQuery NVARCHAR(MAX)

-- Step 1: Gather all numeric columns from PLAYER_TABLE (excluding PLAYER_NAME)
SELECT @AllNumericColumns += QUOTENAME(COLUMN_NAME) + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PLAYER_TABLE'
  AND COLUMN_NAME != 'PLAYER_NAME'
  AND DATA_TYPE IN ('int', 'tinyint', 'smallint', 'decimal', 'numeric')

-- Remove the trailing comma from the column list
SET @AllNumericColumns = LEFT(@AllNumericColumns, LEN(@AllNumericColumns) - 1)

-- Step 2: Use UNPIVOT to convert columns to rows, then filter for non-zero values
SET @UnpivotQuery = '
SELECT @NonZeroColumns += QUOTENAME(ColumnName) + '',''
FROM (
    SELECT ' + @AllNumericColumns + '
    FROM PLAYER_TABLE
    WHERE PLAYER_NAME = ''' + @TargetPlayer + '''
) PlayerStats
UNPIVOT (
    StatValue FOR ColumnName IN (' + @AllNumericColumns + ')
) UnpivotedStats
WHERE StatValue <> 0'

-- Execute the unpivot query to collect non-zero column names
EXEC sp_executesql @UnpivotQuery, N'@NonZeroColumns NVARCHAR(MAX) OUTPUT', @NonZeroColumns OUTPUT

-- Clean up the trailing comma from the non-zero column list
IF LEN(@NonZeroColumns) > 0
    SET @NonZeroColumns = LEFT(@NonZeroColumns, LEN(@NonZeroColumns) - 1)

-- Step 3: Build and run the final SELECT statement
SET @FinalSelectQuery = 'SELECT ' + @NonZeroColumns + ' FROM PLAYER_TABLE WHERE PLAYER_NAME = ''' + @TargetPlayer + ''''

EXEC sp_executesql @FinalSelectQuery

Key Notes: #

• Database Adaptability: If you're using MySQL, PostgreSQL, or another database, the syntax for dynamic SQL will change slightly. For example, MySQL uses PREPARE and EXECUTE instead of sp_executesql, and PostgreSQL uses EXECUTE with string concatenation.
• Data Type Support: This example focuses on numeric columns (since your non-zero values are Score and PlayedMinutes, which are integers). If you need to include non-numeric columns (like checking for non-empty strings), you'll need to adjust the column selection and filtering logic.
• SQL Injection Safety: Since we're pulling column names from the trusted INFORMATION_SCHEMA and using parameter handling where possible, the risk of SQL injection is minimal. Always validate inputs when working with dynamic SQL to stay safe.

Why Static SQL Won't Work #

Static SQL requires you to define exactly which columns to return when you write the query—before it runs. Since the non-zero columns can vary from row to row, there's no way to hardcode this in a static SELECT statement. Dynamic SQL lets you build the query on the fly based on the actual data in the row.

内容的提问来源于stack exchange，提问作者ÇAğrı Keskin