sql - 存储一对多关系的最佳方法-实例/困境

Question

我需要对一个想法进行建模，可以将其分解并考虑如下:

图书详细信息

图书价格

这里的问题是您可以为书籍设置许多价格，并且这些价格可能会发生变化。这是一个例子

图书详细信息:
-----------------
身份证名
1哈利·波特...

这很容易。

更有趣的是，对于这本书，我当天可能会有十种不同的价格，例如:

图书价格:
------------------------------------
Book_Details_Id实物价格
1特别优惠10
1批量购买20
1普通30

我需要在列中提供书籍 list 及其所有价格-类似于:

BookName SpecialOfferBulkPurchaseNormal
哈利·波特…10 20 30

我的问题是:图书价格表是否应该实际上具有所有不同的价格类型作为列？对我来说这很丑陋，一个更好的主意是将每个价格连成一排

如果使用这种方法，我将无法想到通过SQL查询为我生成结果集的方法。我整个上午都在想这件事。

编辑:我在计算价格上没有任何余地-必须将其存储下来。

编辑:这基本上是我可以想到的1-n方法(感谢在下面发表评论)-它实际上是我的想法

SELECT book.bookid，bp1.price，bp2.price from book JOIN bookprice bp1 JOIN bookprice bp2 ON bp1.bookid = book.bookid AND bp1.pricetype = 1 AND bp2.bookid = book.bookid AND bp2.pricetype = 2。 。

问题是十个价格，您将加入十次臭!

Answer 1

这个答案是特定于t-SQL的，可以进行一些改进，但是它适用于SQL 2005。

取消注释注释行，它会像MAGIC一样更新! (好吧，不是魔术，而是令人讨厌的)

DROP TABLE Books
DROP TABLE Prices
DROP TABLE bookpricing

CREATE TABLE Books ( id INT, title VARCHAR(20) )
CREATE TABLE Prices ( id INT, [desc] VARCHAR(20), pricingchange VARCHAR(20))
CREATE TABLE bookpricing ( id INT, bookid INT, priceid INT, bookprice MONEY )

INSERT INTO Books VALUES (1, 'Hi Mom')
--INSERT INTO Books Values (2, 'This is another book') 
INSERT INTO Prices VALUES (1, 'Standard', '1')
INSERT INTO Prices  VALUES (2, 'Discount', '.5')
INSERT INTO Prices VALUES(3, 'HyperMarkup', '1.5')
INSERT INTO prices VALUES(4, 'Remaindered', '.1')

INSERT INTO BookPricing VALUES (1,1,1,20.00)
INSERT INTO BookPricing VALUES (2,1,2,10.00)
INSERT INTO BookPricing VALUES (3,1,3,30.00)
--INSERT INTO BookPricing VALUES (4,2,1,30.00)
--INSERT INTO BookPricing VALUES (5,2,2,15.00)
--INSERT INTO BookPricing VALUES (6,2,4,3.00)

SELECT * FROM bookpricing 

/**这是从 http://www.tsqltutorials.com/pivot.php偷来的**/

DECLARE @columns VARCHAR(max)

SELECT @columns = COALESCE(@columns + ',[' + cast(id as varchar) + ']',
'[' + cast(id as varchar)+ ']')
FROM prices


DECLARE @query VARCHAR(max)

SET @query = '
SELECT * FROM (SELECT BookID, PriceID, BookPrice FROM BookPricing) AS BookTable PIVOT (SUM(bookprice) FOR priceid 
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)