MySql-创建要从多个表读取的视图 [英] MySql - Create view to read from Multiple Tables

问题描述

我已经为发票开出了一些旧的行项目，这些发票不再是最新的，但仍需要引用它们.我认为我需要创建一个VIEW，但并不是真正了解它.有人可以帮忙，这样我就可以运行查询以提取发票，然后提取所有已分配的订单项的总和(无论项目位于哪个表中)?

I have archived some old line items for invoices that are no longer current but still need to reference them. I think I need to create a VIEW but not really understanding it. Can someone help so I can run a query to pull the invoice and then the total of all the line items assigned (no matter what table the items are in)?

CREATE TABLE `Invoice` (
  `Invoice_ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Invoice_CreatedDateTime` DATETIME DEFAULT NULL,
  `Invoice_Status` ENUM('Paid','Sent','Unsent','Hold') DEFAULT NULL,
  `LastUpdatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `LastUpdatedAt` (`LastUpdatedAt`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

CREATE TABLE `Invoice_LineItem` (
  `LineItem_ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `LineItem_ChargeType` VARCHAR(64) NOT NULL DEFAULT '',
  `LineItem_InvoiceID` INT(11) UNSIGNED DEFAULT NULL,
  `LineItem_Amount` DECIMAL(11,4) DEFAULT NULL,
  `LastUpdatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LineItem_ID`),
  KEY `LastUpdatedAt` (`LastUpdatedAt`),
  KEY `LineItem_InvoiceID` (`LineItem_InvoiceID`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

CREATE TABLE `Invoice_LineItem_Archived` (
  `LineItem_ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `LineItem_ChargeType` VARCHAR(64) NOT NULL DEFAULT '',
  `LineItem_InvoiceID` INT(11) UNSIGNED DEFAULT NULL,
  `LineItem_Amount` DECIMAL(11,4) DEFAULT NULL,
  `LastUpdatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LineItem_ID`),
  KEY `LastUpdatedAt` (`LastUpdatedAt`),
  KEY `LineItem_InvoiceID` (`LineItem_InvoiceID`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

通常，我将运行以下查询以获取发票上的应付金额

Typically I would just run the following query to get the amount due on the invoices

SELECT
    Invoice_ID,
    Invoice_CreatedDateTime,
    Invoice_Status,
    (SELECT SUM(LineItem_Amount) AS totAmt FROM Invoice_LineItem WHERE LineItem_InvoiceID=Invoice_ID) AS Invoice_Total
FROM
    Invoice
WHERE
    Invoice_Status='Sent'

我又如何在一个查询中从两个表中选择所有订单项?

Also how can I select all the line items from both tables in one query?

SELECT
    LineItem_ID,
    LineItem_ChargeType,
    LineItem_Amount
FROM
    Invoice_LineItem
WHERE
    LineItem_InvoiceID='1234'

推荐答案

您可以使用

You can use the MERGE Storage Engine to create a virtual table that's the union of two real tables:

CREATE TABLE Invoice_LineItem_All 
(
  `LineItem_ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `LineItem_ChargeType` VARCHAR(64) NOT NULL DEFAULT '',
  `LineItem_InvoiceID` INT(11) UNSIGNED DEFAULT NULL,
  `LineItem_Amount` DECIMAL(11,4) DEFAULT NULL,
  `LastUpdatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY (`LineItem_ID`),
  KEY `LastUpdatedAt` (`LastUpdatedAt`),
  KEY `LineItem_InvoiceID` (`LineItem_InvoiceID`)
) ENGINE=MERGE UNION=(Invoice_LineItem_Archived, Invoice_LineItem);

这篇关于MySql-创建要从多个表读取的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！