请求协助将两个单月数据查询合并为双月对比视图查询

Got it, let's fix this for you. The blank results you're seeing when merging your two queries are almost certainly from using an INNER JOIN—that only keeps rows where a matching Company and Item exist in both months. What you need is to preserve all unique (Company, Item) pairs from either month, then join each month's data to that set so missing entries show up as blank.

Step-by-Step Implementation #

We'll use Common Table Expressions (CTEs) to isolate each month's data first, then create a combined list of all (Company, Item) pairs from both months, and finally left-join each month's data to this combined list.

Example SQL Code (SQL Server; adjust syntax for other databases below)

-- Define the two target dates users will input
DECLARE @FirstMonthDate DATE = '2018-01-01';
DECLARE @SecondMonthDate DATE = '2018-02-01';

-- CTE for first month's data (matches your Query 1)
WITH FirstMonthData AS (
    SELECT
        InvNumber,
        Company,
        Date,
        Item,
        Price,
        Quantity,
        Total
    FROM PurchaseOrders -- Replace with your actual table name
    -- Filter for the entire target month (not just exact date)
    WHERE DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) = DATEFROMPARTS(YEAR(@FirstMonthDate), MONTH(@FirstMonthDate), 1)
),
-- CTE for second month's data (matches your Query 2)
SecondMonthData AS (
    SELECT
        InvNumber,
        Company,
        Date,
        Item,
        Price,
        Quantity,
        Total
    FROM PurchaseOrders
    WHERE DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) = DATEFROMPARTS(YEAR(@SecondMonthDate), MONTH(@SecondMonthDate), 1)
),
-- Get all unique (Company, Item) pairs from both months
AllCompanyItems AS (
    SELECT Company, Item FROM FirstMonthData
    UNION -- Use UNION to avoid duplicate pairs
    SELECT Company, Item FROM SecondMonthData
)
-- Final query to join everything and show side-by-side data
SELECT
    fmd.InvNumber AS [First Month Inv Number],
    fmd.Company,
    fmd.Date AS [First Month Date],
    fmd.Item AS [First Month Item],
    fmd.Price AS [First Month Price],
    fmd.Quantity AS [First Month Quantity],
    fmd.Total AS [First Month Total],
    smd.Date AS [Second Month Date],
    smd.Item AS [Second Month Item],
    smd.Price AS [Second Month Price],
    smd.Quantity AS [Second Month Quantity],
    smd.Total AS [Second Month Total],
    smd.InvNumber AS [Second Month Inv Number]
FROM AllCompanyItems ac
-- Left join to keep all pairs, even if first month has no data
LEFT JOIN FirstMonthData fmd 
    ON ac.Company = fmd.Company AND ac.Item = fmd.Item
-- Left join again for second month's data
LEFT JOIN SecondMonthData smd 
    ON ac.Company = smd.Company AND ac.Item = smd.Item
ORDER BY ac.Company, ac.Item;

Adjustments for Other Databases #

• MySQL: Replace DATEFROMPARTS with DATE_FORMAT(Date, '%Y-%m-01') to target the first day of the month, and declare parameters with SET @FirstMonthDate = '2018-01-01';
• PostgreSQL: Use DATE_TRUNC('month', Date) = DATE_TRUNC('month', @FirstMonthDate) to filter by full month.

Why This Works #

• The AllCompanyItems CTE captures every unique Company + Item combination from either month, so no pairs get excluded.
• LEFT JOIN ensures that even if a company didn't purchase an item in one month, the row still appears with blank (NULL) values for that month's columns—exactly matching your desired output.

Example Output #

This query will return results matching your expected format, including the blank row for XYZ's Chair in the second month:

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