如何在SQL中显示表某列不存在的值并对应显示0？求查询语句

Hey there! Let’s break down your two SQL requirements with practical, adaptable examples—no jargon overload, just straight-up code you can tweak for your setup.

1. Query for non-existent values in a column and show 0 alongside #

First off, to find values that don’t exist in your target column, you’ll need a list of the values you want to check against (since SQL needs to know what "missing" means). Let’s use a common scenario: say you have a products table with a category column, and you want to check which of the categories ('A', 'B', 'C', 'D') don’t show up, with a count of 0 for those missing ones.

Here’s how to do it with a CTE (Common Table Expression) to define your target values, then a left join to pair them with your table data:

-- Define all the values we want to check for
WITH target_values AS (
    SELECT 'A' AS category UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D'
)
SELECT 
    tv.category,
    COALESCE(COUNT(p.category), 0) AS occurrence_count
FROM target_values tv
LEFT JOIN products p ON tv.category = p.category
GROUP BY tv.category;

How this works: #

• The CTE target_values creates a temporary list of all the categories we care about.
• A LEFT JOIN ensures every value from our target list is included in the result, even if there’s no match in the products table.
• COALESCE turns the NULL counts (from missing values) into 0, which is exactly what you need.

2. Generate specific formatted output from a structured table #

Since you didn’t share the exact table structure or desired output format, I’ll use a common example to illustrate—adjust the code to match your actual schema! Let’s say you have a sales table with columns: region, quarter, amount, and you want to pivot the data so each region is a row, with each quarter’s total sales as a column.

Option 1: Conditional aggregation (works in most databases: MySQL, PostgreSQL, SQLite) #

This is the most portable approach, since not all databases support the PIVOT keyword:

SELECT
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1_total,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2_total,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3_total,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4_total,
    SUM(amount) AS annual_total
FROM sales
GROUP BY region
ORDER BY region;

Option 2: PIVOT (for SQL Server, Oracle, etc.) #

If your database supports the PIVOT syntax, this is a cleaner way to get the same result:

SELECT *
FROM (
    SELECT region, quarter, amount
    FROM sales
) AS source_data
PIVOT (
    SUM(amount)
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivoted_sales
ORDER BY region;

Customization tip: #

Swap out the column names (region, quarter, amount), aggregation function (SUM, COUNT, etc.), and conditional values to match your specific table structure and desired output format.

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