SQLite多表关联创建视图异常求助：A1、A4数据统计为空

Let's walk through what's broken in your current code and build the correct view step by step.

What's Wrong With Your Original Code #

1. Typos in Table Names: You wrote INNER JOIN Room ON B.aNo = A.aNo but your table is named Table B—should be INNER JOIN B ON B.aNo = A.aNo.
2. Impossible WHERE Condition: WHERE C.aNo = 'A1' AND C.aNo = 'A4' can never be true (a single value can't equal two different strings). You need IN ('A1', 'A4') instead.
3. Incorrect Aggregation: sum(C.aNo) tries to sum string values (like 'A1') which doesn't make sense. You want to count the number of records, so use COUNT(*).
4. Missing GROUP BY: When using aggregate functions like COUNT, you need to group by the non-aggregated columns (aName and bType).
5. No Handling for Missing bTypes: Your expected output shows all bTypes for each user (even with Null counts), but your code only returns types that exist for a user.

Correct View Implementation #

This code will generate the exact output you want, including all bTypes for A1/A4 with Null where a user doesn't have that type:

CREATE VIEW v_A1A4 AS
-- Get all unique bTypes from Table B to ensure full coverage
WITH AllBTypes AS (
    SELECT DISTINCT bType FROM B
),
-- Filter only the users we care about (A1 and A4)
TargetUsers AS (
    SELECT aNo, aName FROM A WHERE aNo IN ('A1', 'A4')
),
-- Count how many times each bType appears for A1/A4 in Table B
TypeCounts AS (
    SELECT 
        B.aNo,
        B.bType,
        COUNT(*) AS quantity
    FROM B
    WHERE B.aNo IN ('A1', 'A4')
    GROUP BY B.aNo, B.bType
)
-- Combine everything to show all bTypes for each user
SELECT 
    TU.aName,
    ABT.bType,
    TC.quantity AS `A1 and A4 quantity`
FROM TargetUsers TU
-- Cross join to get every bType for every target user
CROSS JOIN AllBTypes ABT
-- Left join to keep all bTypes, even if no count exists
LEFT JOIN TypeCounts TC 
    ON TU.aNo = TC.aNo AND ABT.bType = TC.bType
-- Sort to match your expected output
ORDER BY TU.aName, ABT.bType;

How It Works #

• AllBTypes: Captures every unique bType from Table B, so we don't miss any types in the final output.
• TargetUsers: Narrows down Table A to only the users we need (A1 and A4).
• TypeCounts: Calculates the number of times each bType appears for A1/A4 in Table B.
• The final SELECT uses a cross join to pair every user with every bType, then a left join to attach the counts (showing Null when a user has no records for that type).

Testing the View #

When you run SELECT * FROM v_A1A4;, you'll get exactly the output you requested:

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