如何连接两个表和第三个表显示结果

问题描述：

我已经做了两个Table（Quotation1和Quotation2）和它具有以下Columns像：如何连接两个表和第三个表显示结果

CREATE TABLE Quotation1 (
    Q_No  INTEGER (10), 
    Date  DATE, 
    C_Code VARCHAR (45), 
    C_Name VARCHAR (45), 
    C_Contact VARCHAR (45), 
    G_Tot  VARCHAR (45), 
    Remarks VARCHAR (250) 
); 

CREATE TABLE Quotation2 (
    Q_No   VARCHAR (45), 
    P_Code  VARCHAR (45), 
    P_Name  VARCHAR (45), 
    Price  VARCHAR (45), 
    Qnty   INTEGER (10), 
    Amt   VARCHAR (45), 
    Discount_Per INTEGER (10), 
    Discount  INTEGER (10), 
    VAT_Per  INTEGER (10), 
    VAT   INTEGER (10), 
    Tot_Amt  VARCHAR (45) 
);

我不得不Joins这两个Table并显示其Result在Third TableQuotation其中有以下Columns像：

CREATE TABLE Quotation (
    Q_No   INT (10)  PRIMARY KEY 
           NOT NULL, 
    Date   DATE, 
    C_Code  VARCHAR (45) REFERENCES CustomerInfo (C_Code) ON DELETE NO ACTION 
                   ON UPDATE NO ACTION 
                   MATCH SIMPLE NOT DEFERRABLE INITIALLY DEFERRED, 
    C_Name  VARCHAR (45), 
    C_Contact VARCHAR (45), 
    Remarks  VARCHAR (45), 
    P_Code  VARCHAR (45) REFERENCES ProductInfo (P_Code) ON DELETE NO ACTION 
                   ON UPDATE NO ACTION 
                   MATCH SIMPLE NOT DEFERRABLE INITIALLY DEFERRED, 
    P_Name  VARCHAR (45), 
    Price  VARCHAR (45), 
    Qnty   INTEGER (10), 
    Amount  VARCHAR (45), 
    Discount_Per INTEGER (10), 
    Discount  INTEGER (10), 
    VAT_Per  INTEGER (10), 
    VAT   INTEGER (10), 
    Tot_Amt  VARCHAR (45), 
    G_Tot  VARCHAR (45) 
) 
WITHOUT ROWID;

我已经申请继Query一样：

INSERT INTO Quotation (Q_No,Date,C_Code,C_Name,C_Contact,Remarks,P_Code,P_Name,Price,Qnty,Amount,Discount_Per,Discount,VAT_Per,VAT,Tot_Amt,G_Tot) 
SELECT Quotation2.Q_No, 
     Quotation1.Date, 
     Quotation1.C_Code, 
     Quotation1.C_Name, 
     Quotation1.C_Contact, 
     Quotation1.Remarks, 
     Quotation2.P_Code, 
     Quotation2.P_Name, 
     Quotation2.Price, 
     Quotation2.Qnty, 
     Quotation2.Amt, 
     Quotation2.Discount_Per, 
     Quotation2.Discount, 
     Quotation2.VAT_Per, 
     Quotation2.VAT, 
     Quotation2.Tot_Amt, 
     Quotation1.G_Tot 
FROM Quotation1, 
    Quotation2 
INNER JOIN Quotation ON Quotation1.Q_No=Quotation2.Q_No;

它给人的关注Result我爱：[02:16:23] Query finished in 0.010 second(s).

但不显示任何Data甚至没有Column Name请帮助。

对于初学者..你不应该使用'Date'命名一个字段。这只是一个坏习惯，因为它是MySQL使用的保留术语。我怀疑它会影响你的查询，只是指出一个观察... – Zak

答

为什么在插入时加入目标表？

-- Insert 
INSERT INTO Quotation (Q_No,Date,C_Code,C_Name,C_Contact,Remarks,P_Code,P_Name,Price,Qnty,Amount,Discount_Per,Discount,VAT_Per,VAT,Tot_Amt,G_Tot) 
SELECT Quotation2.Q_No, 
     Quotation1.Date, 
     Quotation1.C_Code, 
     Quotation1.C_Name, 
     Quotation1.C_Contact, 
     Quotation1.Remarks, 
     Quotation2.P_Code, 
     Quotation2.P_Name, 
     Quotation2.Price, 
     Quotation2.Qnty, 
     Quotation2.Amt, 
     Quotation2.Discount_Per, 
     Quotation2.Discount, 
     Quotation2.VAT_Per, 
     Quotation2.VAT, 
     Quotation2.Tot_Amt, 
     Quotation1.G_Tot 
FROM Quotation1 
JOIN Quotation2 ON Quotation1.Q_No = Quotation2.Q_No; 

-- Show results: 
SELECT * FROM Quotation;

应该做的工作。

[20:54:09]对数据库'InventorySystem'执行SQL查询时出错：UNIQUE约束失败：Quotation.Q_No –

这告诉你，你在Quotation1或Quotation2中重复了Q_No。很可能是因为你没有你的约束。你可以通过使用INSERT IGNORE而不是INSERT来消除这个错误**但是要小心，因为这样做会导致数据丢失。 –

感谢它的工作，我已经做了''独特的冲突IGNORE''的改变＃ –

如何连接两个表和第三个表显示结果

相关推荐