mysql - SQL 'merging' 列从结果集中到一个结果集中

Question

我一直在纠结如何在 MySQL 的约束下编写特定的 View 。

以下表格和列很重要:

CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL AUTO INCREMENT,
  PRIMARY KEY (`id`)
)

-- Joins payments to invoices. The sum of all `invoice_currency_value`s is the balance paid towards an invoice.
CREATE TABLE `financial_transactions_invoices` (
  `id` int(10) unsigned NOT NULL AUTO INCREMENT,
  `invoice` int(10) unsigned NOT NULL,
  `invoice_currency_value` decimal(8,2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

-- Lists items (services) available to purchase.
CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO INCREMENT,
  `value` decimal(8,2) unsigned NOT NULL
  PRIMARY KEY (`id`)
)

-- Each instance represents that the `item` has been purchased.
CREATE TABLE `item_instances` (
  `id` int(10) unsigned NOT NULL AUTO INCREMENT,
  `invoice` int(10) unsigned NOT NULL,
  `item` int(10) unsigned NOT NULL,
  `invoice_currency_rate` decimal(11,5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

-- Any number of tax instances can exist for an item instance and indicate this tax has been applied to the associated item instance.
CREATE TABLE `tax_instances` (
  `id` int(10) unsigned NOT NULL AUTO INCREMENT,
  `item_instance` int(10) unsigned NOT NULL,
  `value` decimal(8,2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

现在，我需要一个列出每一行的 View ，

• 发票编号
• 发票总值
• 发票上的总税额
• 以及为发票支付的总金额

但是，我无法弄清楚如何将这三个单独的查询放入每张发票一行的同一结果集中，例如

inv_no  total_value     total_tax       payments
1       150             5               120
2       120             10              20
3       10              0               10
4       1000            150             1150

我已经编写了以下生成所需结果的查询，但由于 MySQL View 中的“无子查询”规则，这是 Not Acceptable 。

SELECT `invoice_id`, SUM(`total_value`) AS `total_value`, SUM(`total_tax`) AS `total_tax`,
    SUM(`paid_balance`) AS `paid_balance`
FROM
(SELECT `invoices`.`id` AS `invoice_id`, SUM(`items`.`value` * `item_instances`.`invoice_currency_rate`) AS `total_value`,
    NULL AS `total_tax`, NULL AS `paid_balance`
FROM `items`
    JOIN `item_instances` ON `items`.`id` = `item_instances`.`item`
    JOIN `invoices` ON `item_instances`.`invoice` = `invoices`.`id`
GROUP BY `invoices`.`id`
UNION
SELECT `invoices`.`id`, NULL, SUM(`tax_instances`.`value`), NULL
FROM `tax_instances`
    JOIN `item_instances` ON `tax_instances`.`item_instance` = `item_instances`.`id`
    JOIN `invoices` ON `item_instances`.`invoice` = `invoices`.`id`
GROUP BY `invoices`.`id`
UNION
SELECT `invoices`.`id`, NULL, NULL, SUM(`financial_transactions_invoices`.`invoice_currency_value`)
FROM `financial_transactions_invoices`
    JOIN `invoices` ON `financial_transactions_invoices`.`invoice` = `invoices`.`id`
GROUP BY `invoices`.`id`) AS `components`
GROUP by `invoice_id`;

如果不按照我的方式解决问题，我想不出任何其他方法可以在 MySQL 中。

有什么想法吗？感谢任何帮助。

Answer 1

您可以创建两个 View 。一个使用 UNION 子查询，一个使用外部查询。