基于三张业务表的MySQL多表求和技术实现问询

Hey there! Let’s walk through how to implement sum operations across your three tables. First, let’s recap the core relationships to make sure we’re on the same page:

• th_sales_inventory is the main sales order table, linked to th_sales_inventory_detail via sales_inventory_id
• th_sales_inventory_detail connects to th_product via product_id

Below are common use cases with SQL examples, tailored to your schema:

1. Calculate Total Sales Amount Across All Valid Orders #

This sums up all line-item amounts from the detail table, filtered to only completed orders (adjust the status value to match your actual status codes):

SELECT 
    SUM(sid.amount) AS total_overall_sales,
    COUNT(DISTINCT si.id) AS total_completed_invoices
FROM th_sales_inventory si
INNER JOIN th_sales_inventory_detail sid 
    ON si.id = sid.sales_inventory_id
WHERE si.status = 'completed'; -- Replace with your active/completed status value

Notes: #

• COUNT(DISTINCT si.id) ensures we don’t double-count orders if a single order has multiple line items.
• Use INNER JOIN here because we only care about orders that have corresponding detail records (invalid orders without details are excluded).

2. Sum Sales by Product (Total Quantity & Revenue) #

This joins all three tables to get product-level sales metrics, great for tracking top-performing items:

SELECT 
    p.id AS product_id,
    p.name AS product_name,
    SUM(sid.qty) AS total_units_sold,
    SUM(sid.amount) AS total_product_revenue
FROM th_product p
INNER JOIN th_sales_inventory_detail sid 
    ON p.id = sid.product_id
INNER JOIN th_sales_inventory si 
    ON sid.sales_inventory_id = si.id
WHERE 
    si.status = 'completed' -- Filter valid orders
    AND si.invoice_date BETWEEN '2024-01-01' AND '2024-12-31' -- Optional date range
GROUP BY p.id, p.name
ORDER BY total_product_revenue DESC;

Notes: #

• Group by p.id and p.name to ensure each product is a single row (critical if ONLY_FULL_GROUP_BY is enabled in your MySQL config).
• Add the date filter to narrow results to a specific period (e.g., quarterly sales).

3. Monthly Sales Summary #

Group sales by month to track trends over time:

SELECT 
    DATE_FORMAT(si.invoice_date, '%Y-%m') AS sales_month,
    SUM(sid.amount) AS monthly_sales_total,
    COUNT(si.id) AS monthly_invoice_count
FROM th_sales_inventory si
INNER JOIN th_sales_inventory_detail sid 
    ON si.id = sid.sales_inventory_id
WHERE si.status = 'completed'
GROUP BY sales_month
ORDER BY sales_month ASC;

Notes: #

• DATE_FORMAT(si.invoice_date, '%Y-%m') converts dates to a "YYYY-MM" format for clean monthly grouping.
• Ordering by sales_month ensures results are in chronological order.

4. Sum Sales by Customer Address #

If you want to see which locations generate the most revenue:

SELECT 
    si.client_address,
    SUM(sid.amount) AS customer_total_sales,
    COUNT(si.id) AS customer_invoice_count
FROM th_sales_inventory si
INNER JOIN th_sales_inventory_detail sid 
    ON si.id = sid.sales_inventory_id
WHERE si.status = 'completed'
GROUP BY si.client_address
ORDER BY customer_total_sales DESC;

Key Best Practices #

• Filter early: Add WHERE clauses to the main th_sales_inventory table first (e.g., status, date ranges) to reduce the number of rows joined with the detail table—this boosts query performance.
• Use LEFT JOIN if needed: If you want to include orders that have no detail records (e.g., draft orders), replace INNER JOIN with LEFT JOIN. Just note that SUM() will return NULL for those orders, so you might want to use COALESCE(SUM(sid.amount), 0) to show 0 instead.
• Validate aggregated values: If your amount field in th_sales_inventory_detail is derived from selling_price * qty, you can alternatively calculate it on the fly with SUM(sid.selling_price * sid.qty) to avoid relying on stored values (useful if the stored amount might be outdated).

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