将数据插入具有多对多关系的表中 [英] inserting data into a table that has a many to many relationship

问题描述

我正在做一项作业，并且需要您在SQL数据库中的以下帮助:-

I am working on an assignment and need your help with the following in SQL database:-

我有3张桌子

1. 产品
2. LintItem
3. 发票

LineItem是一个新娘表，我需要将数据插入LineItem，但它需要ProductID和InvoiceNumber. 就我而言，发票表是空的，它将由LineItem表传递的数据填充.

LineItem is a bride table and I need to insert data into LineItem but it requires ProductID and InvoiceNumber. In my case the Invoice table is emppty and it will be filled from the data that LineItem table passes.

问题是，如何从lineItem表中获取数据之前创建发票?

The problem is how can I create an invoice before having the data from the lineItem table?

我正在使用这些表格进行在线购物.

I am using these table for online shopping cart.

我真的很难解释这个问题.希望你能理解，谢谢！

It's really hard for me to explain this problem. Hope you understand it, Thanks!

推荐答案

听起来您有一个外键约束，在插入订单项记录之前，它会强制存在发票记录.根据您的问题的措辞很难确切地说出来，但可能像这样.

It sounds like you have a foreign key constraint forcing the existence of a Invoice record prior to inserting your line item records. It is hard to say exactly, based on the phrasing of your question but could be something like.

--Table变量，用于保存订单项
声明@lineItems表
(
   发票编号INT，
   数量INT
)

--Table variable to hold line items
DECLARE @lineItems TABLE
(
   InvoiceNumber INT,
   Quantity INT
)

插入@lineitems值(1,1)
插入@lineitems值(1,2)
INSERT INTO @lineitems VALUES(1,1)
INSERT INTO @lineitems VALUES(1,2)
-添加发票记录的第一和总数量等.
插入发票
   SELECT发票编号，SUM(数量)
    来自@lineItems
    GROUP BY发票编号
--ADD INVOICE RECORD FIRST AND SUM Quantities etc....
INSERT INTO Invoice
   SELECT InvoiceNumber,SUM(Quantity)
    FROM @lineItems
    GROUP BY InvoiceNumber
-现在可以添加订单项
插入LineItems SELECT * FROM @lineItems 
--NOW YOU CAN ADD LINE ITEMS
INSERT INTO LineItems SELECT * FROM @lineItems
 
如果这是您的目标，则可以使用此模式.
This is a pattern you could use if that was your goal.
如果您想在用户单击网页上的添加"时即时插入这些LineItem.我不会将您的LineItem SQL表用于这种方式的缓存.在不了解应用程序的情况下很难说，但是您确实应该在HTTP会话或客户端中将这些数据缓存为(数组，json，本地存储等).如果要选择作为SQL表来执行此操作，只需在没有约束的情况下创建一个新的LineItem，然后按照上面的类似方法，可以将该表插入到LineItem表中.
If you are wanting to insert these LineItems on the fly as the user is clicking Add from the webpage.  I wouldn't use your LineItem SQL table for caching this way.  Without knowing anything about your application it is hard to say but you really should be caching this data in the HTTP session or in the client as (array,json, local storage etc..).  If you were to choose to do this as an SQL table just make a new LineItem without the constraints and then similarly per above you can use that table to insert into your LineItem table.

                        
这篇关于将数据插入具有多对多关系的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！