作者:丫头片子ZXH | 来源:互联网 | 2023-05-18 03:30
--仓库表CREATETABLE#t_Stock(FItemIDINTIDENTITY,FNumberVARCHAR(10),FNameVARCHAR(20))INSERT#t_S
--仓库表
CREATE TABLE #t_Stock (FItemID INT IDENTITY,FNumber VARCHAR(10),FName VARCHAR(20))
INSERT #t_Stock (FNumber,FName) VALUES('1001','原料仓')
INSERT #t_Stock (FNumber,FName) VALUES('1002','半成品仓')
INSERT #t_Stock (FNumber,FName) VALUES('1001','成品仓')
--SELECT * FROM #t_Stock
--物料表
CREATE TABLE #t_ICItem (FItemID INT IDENTITY,FNumber VARCHAR(10),FName VARCHAR(20))
INSERT #t_ICItem (FNumber,FName) VALUES('1001','物料1001')
INSERT #t_ICItem (FNumber,FName) VALUES('1002','物料1002')
INSERT #t_ICItem (FNumber,FName) VALUES('1003','物料1003')
--SELECT * FROM #t_ICItem
--库存表
CREATE TABLE #ICInv (FItemID INT,FStockID INT,FQty DECIMAL(20,8))
INSERT #ICInv (FItemID,FStockID,FQty) VALUES(1,1,20)
INSERT #ICInv (FItemID,FStockID,FQty) VALUES(2,1,30)
INSERT #ICInv (FItemID,FStockID,FQty) VALUES(1,2,50)
--SELECT * FROM #ICInv
declare @sql varchar(8000)
set @sql = 'select fitemid as ' + '物料内码'
select @sql= @sql+' , max (case FStockID when ' + str(FStockID) + ' then Fqty else 0 end ) [' + FName + ']'
from (select distinct t1.FItemID,t1.FStockID,t1.FQty ,t2.FName from #ICInv t1 inner join #t_Stock t2 on t1.FStockID =t2.FItemID ) as a
set @sql = @sql + ' from #ICInv group by fitemid'
execute (@sql)
物料内码 原料仓 半成品仓 原料仓
1 20.00000000 50.00000000 20.00000000
2 30.00000000 0.00000000 30.00000000
如何去掉重复的列“原料仓”?
请高手赐教!!
5 个解决方案
select distinct t1.FItemID,t1.FStockID,t1.FQty ,t2.FName from #ICInv t1 inner join #t_Stock t2 on t1.FStockID =t2.FItemID
你子查询中的distinct没有起到过滤作用。楼上写的就是OK的
select distinct t1.FItemID,t1.FStockID,t1.FQty ,t2.FName from #ICInv t1 inner join #t_Stock t2 on t1.FStockID =t2.FItemID
关键在于这个子查询。这个子查询中关键是要把重复的行去掉,但是你select列表后面的列太多,导致distinct没有把重复的fitemid完全去掉
后面这段改成这样就好了
declare @sql varchar(8000)
set @sql = 'select fitemid as ' + '物料内码'
select @sql= @sql+' , max (case FStockID when ' + str(FStockID) + ' then Fqty else 0 end ) [' + FName + ']'
from (select distinct t1.FStockID,t2.FName from #ICInv t1 inner join #t_Stock t2 on t1.FStockID =t2.FItemID ) as a
set @sql = @sql + ' from #ICInv group by fitemid'
execute (@sql)