mysql - MySQL View :多态关联和子表

如何创建视图（producer_view和retailer_view），显示当它们是供应商和客户时每笔交易的总金额SUM( unit_price * quantity )？

producer_view
+------+-------------------+-----------------+
| name | as_supplier_total | as_client_total |
+------+-------------------+-----------------+
| foo  |               144 |               9 |
| bar  |                 9 |             264 |
+------+-------------------+-----------------+

retailer_view
+------+-------------------+-----------------+
| name | as_supplier_total | as_client_total |
+------+-------------------+-----------------+
| baz  |                16 |             125 |
| qux  |               245 |              16 |
+------+-------------------+-----------------+

我的两个困难是：
每个事务的supplier_id和client_id存储在两个多态关联中，其中supplier_type和client_type可以是App\Producer或App\Retailer。
每个事务的unit_price和quantity存储在子表中。
两个父表是：

Producer
+----+------+
| id | name |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+

Retailer
+----+------+
| id | name |
+----+------+
|  1 | baz  |
|  2 | qux  |
+----+------+

在交易中，生产商和零售商可以是供应商或客户。

Transaction
+----+---------+-------------+---------------+-----------+--------------+
| id | product | supplier_id | supplier_type | client_id |  client_type |
+----+---------+-------------+---------------+-----------+--------------+
|  1 | a       |           1 |  App\Producer |         1 | App\Retailer |
|  2 | b       |           2 |  App\Retailer |         1 | App\Retailer |
|  3 | c       |           1 |  App\Producer |         2 | App\Producer |
|  4 | d       |           2 |  App\Retailer |         2 | App\Producer |
+----+---------+-------------+---------------+-----------+--------------+

每个事务的详细信息记录在项目表中：

Item
+----+----------------+------------+----------+
| id | transaction_id | unit_price | quantity |
+----+----------------+------------+----------+
|  1 |              1 |         10 |        1 |
|  2 |              1 |         20 |        1 |
|  3 |              2 |         30 |        1 |
|  4 |              2 |         40 |        1 |
|  5 |              3 |         50 |        1 |
|  6 |              3 |         60 |        1 |
|  7 |              4 |         70 |        1 |
|  8 |              4 |         80 |        1 |
|  9 |              5 |          2 |        2 |
| 10 |              6 |          3 |        3 |
| 11 |              7 |          4 |        4 |
| 12 |              8 |          5 |        5 |
+----+----------------+------------+----------+

SQLFiddle来自下面的验证答案：
producer_view
retailer_view

最佳答案

尝试以下查询：
制作人：

SELECT
  p.name,
  SUM(i.quantity * i.unit_price) AS as_supplier_total,
  cagg.as_client_total
FROM producer p
LEFT JOIN transaction t on t.supplier_id = p.id AND t.supplier_type = 'App\\Producer'
LEFT JOIN item i ON t.id = i.transaction_id
LEFT JOIN (
  SELECT
    p.id,
    SUM(i.quantity * i.unit_price) AS as_client_total
  FROM producer p
  LEFT JOIN transaction t on t.client_id = p.id AND t.client_type = 'App\\Producer'
  LEFT JOIN item i ON t.id = i.transaction_id
  GROUP BY p.id
) AS cagg ON cagg.id = p.id
GROUP BY p.id

对于零售商：

SELECT
  r.name,
  SUM(i.quantity * i.unit_price) AS as_supplier_total,
  cagg.as_client_total
FROM retailer r
LEFT JOIN transaction t on t.supplier_id = r.id AND t.supplier_type = 'App\\Retailer'
LEFT JOIN item i ON t.id = i.transaction_id
LEFT JOIN (
  SELECT
    r.id,
    SUM(i.quantity * i.unit_price) AS as_client_total
  FROM retailer r
  LEFT JOIN transaction t on t.client_id = r.id AND t.client_type = 'App\\Retailer'
  LEFT JOIN item i ON t.id = i.transaction_id
  GROUP BY r.id
) AS cagg ON cagg.id = r.id
GROUP BY r.id

关于mysql - MySQL View :多态关联和子表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49539899/