如何基于多态关联与子表创建MySQL交易总额统计视图？

Got it, let's walk through this step by step. First, I'll assume the standard table structure for a polymorphic association scenario (adjust table/column names if your schema differs):

Assumed Base Table Structure #

These are the typical tables you'd have for this setup:

• producers: Stores producer entities

  CREATE TABLE producers (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL
  );
  

• retailers: Stores retailer entities

  CREATE TABLE retailers (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL
  );
  

• transactions: Tracks transactions with polymorphic links to suppliers/clients

  CREATE TABLE transactions (
      id INT PRIMARY KEY AUTO_INCREMENT,
      supplier_type VARCHAR(20) NOT NULL, -- Values like 'Producer' or 'Retailer'
      supplier_id INT NOT NULL,
      client_type VARCHAR(20) NOT NULL, -- Values like 'Producer' or 'Retailer'
      client_id INT NOT NULL,
      unit_price DECIMAL(10,2) NOT NULL,
      quantity INT NOT NULL
  );
  

1. Create producer_view #

This view calculates total transaction amounts for each producer when they act as a supplier, and when they act as a client. We use LEFT JOIN to make sure every producer shows up even if they have no transactions in either role.

CREATE VIEW producer_view AS
SELECT
    p.name,
    COALESCE(SUM(CASE WHEN t.supplier_type = 'Producer' AND t.supplier_id = p.id THEN t.unit_price * t.quantity END), 0) AS as_supplier_total,
    COALESCE(SUM(CASE WHEN t.client_type = 'Producer' AND t.client_id = p.id THEN t.unit_price * t.quantity END), 0) AS as_client_total
FROM producers p
LEFT JOIN transactions t
    ON (t.supplier_type = 'Producer' AND t.supplier_id = p.id)
    OR (t.client_type = 'Producer' AND t.client_id = p.id)
GROUP BY p.id, p.name;

Quick breakdown: #

• COALESCE(..., 0) replaces NULL with 0 for producers who have no transactions in a specific role
• The CASE statements filter transactions to only count those where the producer is the supplier or client
• GROUP BY p.id, p.name groups results to show one row per producer

2. Create retailer_view #

This uses the exact same logic as the producer view, but targets the retailers table instead:

CREATE VIEW retailer_view AS
SELECT
    r.name,
    COALESCE(SUM(CASE WHEN t.supplier_type = 'Retailer' AND t.supplier_id = r.id THEN t.unit_price * t.quantity END), 0) AS as_supplier_total,
    COALESCE(SUM(CASE WHEN t.client_type = 'Retailer' AND t.client_id = r.id THEN t.unit_price * t.quantity END), 0) AS as_client_total
FROM retailers r
LEFT JOIN transactions t
    ON (t.supplier_type = 'Retailer' AND t.supplier_id = r.id)
    OR (t.client_type = 'Retailer' AND t.client_id = r.id)
GROUP BY r.id, r.name;

Testing the Views #

Once created, you can query them just like regular tables:

SELECT * FROM producer_view;
SELECT * FROM retailer_view;

This will return results matching the format you provided, with clear totals for each entity's role in transactions.

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