商品库存SKU

一种解决方案(性能垃圾，基本实现功能)

商品表  属性集表 属性表 属性值表 SKU表 SKU选项表  属性集和属性之间的中间表
表关系
商品表  *————–1  属性集表
属性集表 *——————* 属性表
属性表 1 ————–* 属性值表
商品表  1—————-* SKU
SKU 1——-* SKU选项{理解成多对多，会麻烦；需要把所有的可能组合写到选项中之后创建中间表}

商品表

CREATE TABLE Products
([ID] [int] IDENTITY(1,1) NOT NULL,–主键[Name] [nvarchar](50) NOT NULL,–名称[Describe] [nvarchar](max) NULL,–描述[PropertiesSetID] [int] NOT NULL–属性集id
） 

属性集合表（属性的组合，便于选择属性并选择属性对应的值）

CREATE TABLE PropertiesSet
([ID] [int] IDENTITY(1,1) NOT NULL,–主键

[Describe] [nvarchar](max) NULL–描述
） 

属性表 CREATE TABLE Property
([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](50) NOT NULL,[Describe] [nvarchar](max) NULL
） 

属性值表

 CREATE TABLE PropertyValue
([ID] [int] IDENTITY(1,1) NOT NULL,[Value] [nvarchar](50) NOT NULL,

[PropertyID] [int] NOT NULL,
）

属性集和属性中间表

CREATE TABLE SetBtProperty
([SetBtPropertiesID] [int] IDENTITY(1,1) NOT NULL,[SetID] [int] NOT NULL,

[PropertyID] [int] NOT NULL,
） 

 SKU表

CREATE TABLE  SKU
([SKUID] [nvarchar](128) NOT NULL,[Price] [money] NOT NULL,[Number] [int] NOT NULL,

[ProductID] [int] NOT NULL,
）

SKU选项表

CREATE TABLE  SKUOptions
([SKUID] [nvarchar](128) NOT NULL,[ID] [int] IDENTITY(1,1) NOT NULL,[PropertyID] [int] NOT NULL,

[PropertyValueID] [int] NOT NULL
）

商家添加商品
商家会填入一些信息
商品名称，再通过 属性集合来便捷选择该商品需要的属性 ，商家选择各个属性对应的值
传递给sqlserver添加商品

商家可以添加属性集合

create procedure [dbo].[sp_CreatePropertySet](@PropertyIDS nvarchar(512),–属性的参数集合 通过 ID；ID的方式进行拼接的字符串@SetDescribe nvarchar(512)–描述要创建的 属性集合)as begin–这里这样可能没有直接 插入库简便，为了练习 表变量和遍历  insert into PropertiesSet (Describe) values (@SetDescribe)–添加属性集 PropertySet declare @setID int ; set @setID=@@IDENTITY–表示刚插入的集合的id  –sqlserver中没有集合List的概念，但是有些时候需要用到 类似于【集合,数组】的功能，比如这个@PropertyIDS，通过 ID；ID的方式进行拼接的字符串 需要进行 ；切割获取到 每个属性的ID，之后在需要的地方还需要取出来使用  在c#中能够使用List；sqlserver中可以通过【表变量】来实现集合和数组的功能  –截取属性集字符串放入【“数组”】declare @StorePropertyIDS table –声明存储 PropertyID的表变量(id int,–模拟数组集合的索引 0 1 2。。。PropertyID int–值) declare @conditionNum intset @conditionNum=-1 while(@conditionNum<>0)–charindex得不到就返回0，不同于c#返回-1begindeclare @location int         set @location = charindex(‘；’,@PropertyIDS)–存储；出现的位置 12;123;23568        if(@location<=1)begindeclare @str nvarchar(512) = substring(@PropertyIDS,1,len(@PropertyIDS))  insert into @StorePropertyIDS (id,PropertyID) values (@conditionNum+1,cast(@str as int))  set @PropertyIDS= substring(@PropertyIDS,@location+1,len(@PropertyIDS)-@location)–重新赋值 ids组合字符串 endelse–》1begindeclare @str2 nvarchar(512)=SUBSTRING(@PropertyIDS,1,@location-1) insert into @StorePropertyIDS (id,PropertyID) values (@conditionNum+1,cast(@str2 as int))set @PropertyIDS= substring(@PropertyIDS,@location+1,len(@PropertyIDS)-@location)–重新赋值 ids组合字符串end               set @conditionNum = @location–while的条件使用，当charindex没有的时候不不需要再循环了   –遍历表变量插入 中间表–sqlserver中遍历一个表的方法      declare @id int;declare @ProID int;declare @nec bit;  while exists(select id from @StorePropertyIDS)beginset rowcount 1select @id = id,@ProID=PropertyID from @StorePropertyIDSset rowcount 0delete from @StorePropertyIDS where id = @id insert into SetBtProperty (SetID,PropertyID) values(@setID,@ProID)end end end

开始添加商品

create procedure [dbo].[sp_AddProduct](@num int,–数量@price money,–价格@productID int,–商品id@setID int,–属性集合id@options nvarchar(612),–属性;值得组合， 如 颜色id#对应的值id；想好id#对应的值id@name nvarchar(168),–商品名称@describe nvarchar(614)–商品描述)as begin–{declare @pidExists int;select @pidExists = count(1) from Products where ID=@productIDif(@pidExists=0)–不存在begininsert into Products(Name,Describe,PropertiesSetID) values (@name,@describe,@setID)set @productID = @@IDENTITYend  declare @pv table(id int identity(1,1),propID int,pvalue int)–声明表变量，把截取得到的属性编号和值编号存起来，类似于代码中的集合的作用Listdeclare @pv2 table(id int identity(1,1),propID int,pvalue int)–由于遍历一次要删掉，要用2次就声明了两个declare @tb table(id int identity(1,1),skuid nvarchar(128))–获取属性值得组合,属性的个数等在代码中进行控制  [1#1;2#5;13#23]declare @whileNum int=-1 –截取@options插入到表变量@pvwhile (@whileNum<>0)begin–{declare @kv nvarchar(128) –存储截取后的前部分的字符串declare @location int =charindex(N’；’,@options)–表示；在options中的位置，sqlserver中的位置是从1开始计算的if(@location=0)–没有了；begin–{set @kv =  substring(@options,1,len(@options))insert into @pv(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv表变量insert into @pv2(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv2表变量end–}else–能够截取到；begin–{set @kv = substring(@options,1,@location-1)insert into @pv(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv表变量insert into @pv2(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv2表变量    set @options = SUBSTRING(@options,@location+1,len(@options)-@location);–重新个options赋值end–}set @whileNum = @location –当没有；时候charindex（）返回0end–} declare @sqlStr nvarchar(1000)=”–用来根据id+属性+值获取sku信息的语句–根据商品id+属性+属性值 来获取skuid，是否存在，存在则修改；不存则添加新的skuiddeclare @id int;declare @propID int;declare @pvalue intwhile exists(select id from @pv)–遍历白表变量begin–{set rowcount 1select @id=id,@propID=propid,@pvalue=pvalue from @pvset rowcount 0delete from @pv where id = @idset @sqlStr =@sqlStr+ N’select skuid from SKUOptions where PropertyID =’+cast(@propID as nvarchar(32))+N’ and PropertyValueID =’+cast(@pvalue as nvarchar(32))+’ intersect ‘end–}set @sqlStr = substring(@sqlStr,1,len(@sqlStr)-10)–把最后的intersect去掉insert into @tbexec sp_executesql @sqlStr–把sp_executesql得到的结果集插入表变量@tb中 declare @HaveSKUID nvarchar(128);select @HaveSKUID = skuid from SKU where ProductID=@productID and skuid in (select skuid from @tb) if(@HaveSKUID is null)–不存在begin–{declare @newid nvarchar(128)=NEWID()insert into SKU (SKUID,Price,Number,ProductID) values (@newid,@price,@num,@productID)–插入sku表–遍历pv2插入skuoption表 while exists(select id from @pv2)begin–{set rowcount 1    select @id=id,@propID=propid,@pvalue=pvalue from @pv2    set rowcount 0    delete from @pv2 where id = @idinsert into SKUOptions(SKUID,PropertyID,PropertyValueID) values(@newid,@propID,@pvalue)–插入skuoption表end–}end–}else–存在begin–{update SKU set Number=Number+@num where SKUID=@HaveSKUID–只需要修改数量即可end–} end–}

加入购物车，此时 能够得到的参数是 商品id  买家选该商品的属性和值

 create proc [dbo].[sp_GetSKUFromPropIdAndProperty]–根据商品id和属性获取sku信息，根据skuid的情况一般的sql就能够满足(@productID int,–商品id@options nvarchar(612)–属性;值得组合， 如 颜色id#对应的值id；想好id#对应的值id )asbegin–{declare @pv table(id int identity(1,1),propID int,pvalue int)–声明表变量，把截取得到的属性编号和值编号存起来，类似于代码中的集合的作用Listdeclare @tb table(id int identity(1,1),skuid nvarchar(128))–声明表变量，存储sku信息declare @whileNum int=-1–截取@options插入到表变量@pvwhile (@whileNum<>0)begin–{declare @kv nvarchar(128) –存储截取后的前部分的字符串declare @location int =charindex(N’；’,@options)–表示；在options中的位置，sqlserver中的位置是从1开始计算的if(@location=0)–没有了；begin–{set @kv =  substring(@options,1,len(@options))insert into @pv(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv表变量 end–}else–能够截取到；begin–{set @kv = substring(@options,1,@location-1)insert into @pv(propID,pvalue) values (cast(SUBSTRING(@kv,1,charindex(N’#’,@kv)-1) as int),SUBSTRING(@kv,charindex(N’#’,@kv)+1,len(@kv)-charindex(N’#’,@kv)))–插入@pv表变量     set @options = SUBSTRING(@options,@location+1,len(@options)-@location);–重新个options赋值end–}set @whileNum = @location –当没有；时候charindex（）返回0end–} declare @sqlStr nvarchar(1000)=”–用来根据id+属性+值获取sku信息的语句–根据商品id+属性+属性值 来获取skuid，是否存在，存在则修改；不存则添加新的skuiddeclare @id int;declare @propID int;declare @pvalue intwhile exists(select id from @pv)–遍历白表变量begin–{set rowcount 1select @id=id,@propID=propid,@pvalue=pvalue from @pvset rowcount 0delete from @pv where id = @idset @sqlStr =@sqlStr+ N’select skuid from SKUOptions where PropertyID =’+cast(@propID as nvarchar(32))+N’ and PropertyValueID =’+cast(@pvalue as nvarchar(32))+’ intersect ‘end–}set @sqlStr = substring(@sqlStr,1,len(@sqlStr)-10)–把最后的intersect去掉insert into @tbexec sp_executesql @sqlStr–把sp_executesql得到的结果集插入表变量@tb中select * from SKU where ProductID=@productID and skuid in (select skuid from @tb)

end–} 

当然购物车中或者其他地方能够得到skuid，直接通过他查询即可，直接where查询完事..