PostgreSQL中如何关联两表并按采购订单年份获取聚合结果

Hey Kevin, let's get this sorted out! The core issue here is making sure you're correctly joining the two tables and properly extracting the year from date_po for your grouping logic. Let's break this down step by step.

First, you need to confirm the relationship between Purchase and PurchaseOrder—most likely, there's a foreign key in one table linking to the other. For example, maybe Purchase has a purchase_order_id column that references PurchaseOrder.id. I'll use that as the example, but swap it out for your actual foreign key if it's different.

Basic Query Structure (MySQL/MariaDB) #

This uses YEAR() to pull the year from date_po, joins the tables, and groups by that year:

SELECT
    YEAR(po.date_po) AS order_year,
    COUNT(p.id) AS total_purchases, -- Replace with your actual aggregate (SUM, AVG, etc.)
    SUM(p.total_amount) AS total_spent -- Adjust based on your Purchase table columns
FROM
    Purchase p
INNER JOIN
    PurchaseOrder po ON p.purchase_order_id = po.id -- Critical: use your real join condition
GROUP BY
    YEAR(po.date_po)
ORDER BY
    order_year ASC;

For PostgreSQL or SQL Server #

If you're using a different SQL dialect, the year extraction function changes slightly:

• PostgreSQL: Use EXTRACT(YEAR FROM po.date_po) and cast to integer for cleaner grouping:

  SELECT
      EXTRACT(YEAR FROM po.date_po)::INT AS order_year,
      COUNT(p.id) AS total_purchases,
      SUM(p.total_amount) AS total_spent
  FROM
      Purchase p
  INNER JOIN
      PurchaseOrder po ON p.purchase_order_id = po.id
  GROUP BY
      EXTRACT(YEAR FROM po.date_po)
  ORDER BY
      order_year;
  

• SQL Server: YEAR(po.date_po) works here too, same as MySQL:

  SELECT
      YEAR(po.date_po) AS order_year,
      COUNT(p.id) AS total_purchases,
      SUM(p.total_amount) AS total_spent
  FROM
      Purchase p
  INNER JOIN
      PurchaseOrder po ON p.purchase_order_id = po.id
  GROUP BY
      YEAR(po.date_po)
  ORDER BY
      order_year;
  

Common Pitfalls to Check #

• Incorrect Join Condition: If your query isn't returning any results, double-check that your foreign key and primary key match (e.g., maybe it's po.purchase_id = p.id instead of the other way around).
• Handling NULL Dates: If you use LEFT JOIN instead of INNER JOIN, some date_po values might be NULL. Add WHERE po.date_po IS NOT NULL to exclude those from grouping if needed.
• Aggregate Function Issues: Make sure all non-grouped columns in your SELECT are wrapped in aggregate functions (COUNT, SUM, etc.)—otherwise, you'll get errors in strict SQL modes.

Let me know if you need to adjust this based on your specific table schema or SQL dialect!

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