如何在SQL Server 2008的同一列中添加多个项目? [英] How to add Multiple items in same column in sql server 2008?

问题描述

你好

我正在开发用于大学项目的在线计费软件.一切正常，但我遇到了一个问题.客户可能购买了1个以上的项目.假设客户购买了5个项目，那么我如何将所有这些购买的项目存储在同一列中，并总计所有这些项目.

希望您理解我想说的话..

Hello

I am developing Online Billing software for college project.Everything is working fine but I am facing one problem. Customer may purchase more than 1 item.suppose customer purchase 5 items so how can I store all these purchased Items in same column and Make a total of all these items.

I hope you understand what I am trying to say..

推荐答案

您可以创建2个表tblBill和tblBillDetail

tblBill将存储信息，例如customerID，BillDate或与帐单相同的任何其他字段.

tblBillDetail将像否一样存储账单明细.购买的项目数量(数量)，每个项目的单价(单位价格)，购买的每个项目的总金额等.表tblBillDetail中的BillID列将购买的项目链接到客户.

You can Create 2 tables tblBill and tblBillDetail

tblBill will store information like customerID, BillDate or any other field that will be same for a bill.

tblBillDetail will store the Bill details like no. of items purchased (Quantiy), unit price of each item(UnitPrice), Total amount of each item purchased etc. The BillID column in table tblBillDetail links the Purchased items to the customer.

CREATE TABLE tblBill
(
	BillID INT IDENTITY(1,1) CONSTRAINT PK_tblBill PRIMARY KEY,
	CustomerID INT,
	BillDate DATETIME
)


CREATE TABLE tblBillDetail
(
	BillDetail INT IDENTITY(1,1) CONSTRAINT PK_BillDetail PRIMARY KEY,
	BillID INT CONSTRAINT FK_tblBillDetail_tblBill FOREIGN KEY REFERENCES tblBill(BillID),
	ProductDescription VARCHAR(100),
	Quantity DECIMAL(16,2),
	UnitPrice DECIMAL(16,2),
	UOM VARCHAR(50),
	ExtendedAmount DECIMAL(16,2),
)

考虑一个示例，客户购买了5个牙刷和2个牙膏.
数据将被插入表中，如下所示

Consider an example where a customer purchases 5 Tooth brushes and 2 Tooth paste.
The data will be inserted into the tables as shown below

INSERT INTO tblBill (CustomerID, BillDate) VALUES (1,GETDATE())

--Get the BillID that was generated and assign it to @BillID
SELECT @BillID = SCOPE_IDENTITY()

INSERT INTO tblBillDetail (BillID, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount) VALUES (@BillID, 'Tooth Brush', 5, 3.45, 'EACH', 5 * 3.45)

INSERT INTO tblBillDetail (BillID, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount) VALUES (@BillID, 'Tooth Paste', 2,12.95, 'EACH', 2 * 12.95)

查询两个表以检索所需的信息.

Query the two tables to retrieve the information you need.

SELECT B.BillID, CustomerID, BillDate, BillTotal, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount FROM tblBill B
INNER JOIN tblBillDetail D ON B.BillID = D.BillID
INNER JOIN
(
    SELECT BillID, SUM(ExtendedAmount) AS BillTotal FROM tblBillDetail
    GROUP BY BillID
) X ON B.BillID = X.BillID

像这样创建表
创建表购买
(Billid int Primarykey，
CustomerId int int，
ItemId int，
NoOfItems int
)


之后，您可以为单个客户插入多行.
插入Purchase(1,1,123,2)
插入购买(2,1,125,3)

并且以下查询将给出单个客户的商品总数
从CustomerId的Purchase组中选择customerId，sum(NoofItems).

Create table like this
Create table purchase
(Billid int Primarykey,
CustomerId int int,
ItemId int,
NoOfItems int
)


After that you can insert multiple rows for single customer.
Insert into Purchase(1,1,123,2)
Insert into Purchase(2,1,125,3)

and the below query will give the total no of items for a single customer
Select customerId,sum(NoofItems) from Purchase group by CustomerId.

您能解释一下我无法理解您的问题吗?您需要在数据库或用户界面页面中计算物料成本.

can you explain your question i can''t understand your requirement. You need to calculate the items cost in db or UI page.

这篇关于如何在SQL Server 2008的同一列中添加多个项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！