多关联表数据查询需求：按条件汇总金额并按月分组

Let's walk through how to build this query step by step—we need to pull together data from multiple linked tables, calculate aggregated totals, and format the results to match your desired cross-tab layout.

Breakdown of the Approach #

1. Combine Claim Data: First, we'll merge the Petty_claim and Vendors_claim tables since we need to sum their amounts together.
2. Join Related Tables: Link this combined claim data to Invoice_main (to get office and date info), Office (to map IDs to actual office names), and Expense_Types (to get readable expense type labels).
3. Aggregate & Pivot: Use conditional aggregation to turn office-specific totals into separate columns, then group by month and expense type.

Full SQL Query #

SELECT
    -- Extract month from invoice date (adjust function for your SQL dialect)
    EXTRACT(MONTH FROM im.Date) AS Month,
    et.dept_id AS Expense_Type,
    -- Sum totals for each office (adjust labels/conditions to match your needs)
    SUM(CASE WHEN o.Office = 'Support' THEN c.Amount ELSE 0 END) AS Office_1,
    SUM(CASE WHEN o.Office = 'HR' THEN c.Amount ELSE 0 END) AS Office_2,
    SUM(CASE WHEN o.Office = 'Billing' THEN c.Amount ELSE 0 END) AS Office_3
FROM
    -- Union both claim tables to get a single list of all claims
    (
        SELECT invoice_id, Expense_id, Amount FROM Petty_claim
        UNION ALL
        SELECT invoice_id, Expense_id, Amount FROM Vendors_claim
    ) c
-- Join to get invoice details (office assignment and date)
JOIN Invoice_main im ON c.invoice_id = im.invoice_ID
-- Join to map office IDs to human-readable names
JOIN Office o ON im.Office_ID = o.office_ID
-- Join to map expense IDs to type names
JOIN Expense_Types et ON c.Expense_id = et.expense_id
-- Filter for your target expense types and offices (customize these values)
WHERE
    et.dept_id IN ('Type A', 'Type B', 'Type C')
    AND o.Office IN ('Support', 'HR')
GROUP BY
    EXTRACT(MONTH FROM im.Date),
    et.dept_id
ORDER BY
    Month,
    Expense_Type;

Key Details to Customize #

• SQL Dialect Adjustments:
  • Use MONTH(im.Date) instead of EXTRACT(MONTH FROM im.Date) if you're using SQL Server.
  • Oracle supports EXTRACT(MONTH FROM im.Date) as written.
• Office Column Labels: If you want to use Office_1, Office_2 instead of actual office names, swap the CASE WHEN condition to check o.office_ID = 1 instead of o.Office = 'Support'.
• Dynamic Offices: If you have a variable number of offices and don't want to hardcode columns, you'll need dynamic SQL (the exact syntax varies by database—let me know if you need help with that!).
• Filtering: Tweak the WHERE clause to target only the expense types and offices you care about.

Example Output (Using Your Sample Data) #

Running this query with your test data would return:

This matches the structure you outlined—you can remove the Office_3 column if you don't need billing office totals.

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