这个sql怎么写？

Question

这个是一句sql，另外，你写的结果最后的一个数字，都计算错了：
生成的结果如下所示：
每日剩余
产品id Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8 Day9
1  990  970  940  900  850  790  720  640  530
2  9900 9700 9400 90000 8500 7900 7200 6400 5300
3  99990 99970 99940 99900 99850 99790 99720 99640 99530
select 1 产品ID,1000 总量 into [#库存表]
union select 2,10000
union select 3,100000select 1 产品id,10 Day1,20 Day2,30 Day3,40 Day4,50 Day5,60 Day6,70 Day7,80 Day8,90 Day9 
into [#日销量表]
union select 2 ,100 ,200 ,300 ,400 ,500 ,600 ,700 ,800 ,900
union select 3 ,10 ,20 ,30 ,40 ,50 ,60 ,70 ,80 ,90
go
select a.产品id,
       b.总量- a.Day1 as day1,
       b.总量- a.Day1-a.Day2 as day2,
       b.总量- a.Day1-a.Day2-a.Day3 as day3,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4 as day4,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4-a.day5 as day5,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4-a.day5-a.Day6 as day6,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4-a.day5-a.Day6-a.Day7 as day7,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4-a.day5-a.Day6-a.Day7-a.Day8 as day8,
       b.总量- a.Day1-a.Day2-a.Day3-a.Day4-a.day5-a.Day6-a.Day7-a.Day8-a.Day9 as day9
       
from [#日销量表] a
inner join [#库存表] b
        on a.产品id = b.产品id
/*
产品id day1 day2 day3 day4 day5 day6 day7 day8 day9
1 990 970 940 900 850 790 720 640 550
2 9900 9700 9400 9000 8500 7900 7200 6400 5500
3 99990 99970 99940 99900 99850 99790 99720 99640 99550
*/   

Answer 1

select 1 产品ID,1000 总量 into [#库存表]
union select 2,10000
union select 3,100000
 
select 1 产品id,10 Day1,20 Day2,30 Day3,40 Day4,50 Day5,60 Day6,70 Day7,80 Day8,90 Day9 into [#日销量表]
union select 2 ,100 ,200 ,300 ,400 ,500 ,600 ,700 ,800 ,900
union select 3 ,10 ,20 ,30 ,40 ,50 ,60 ,70 ,80 ,90--查詢語句：
;WITH a1 AS
(
SELECT 产品id,day_,日销量
FROM [#日销量表] 
unpivot (日销量 FOR day_ IN (Day1,Day2,Day3,Day4,Day5,Day6,Day7,Day8,Day9)) upvt
)
,a2 AS
(
SELECT a.产品id,a.day_,b.总量-(select SUM(日销量) FROM a1 WHERE 产品id=a.产品id AND day_<=a.day_) end_qty
FROM a1 a
JOIN [#库存表] b ON a.产品id=b.产品id
)
SELECT *
FROM a2
PIVOT (MAX(end_qty) FOR day_ IN (Day1,Day2,Day3,Day4,Day5,Day6,Day7,Day8,Day9)) pvt

Answer 2

1、建表CREATE TABLE [dbo].[kucun](
[cpid] [varchar](50) NULL,
[zl] [int] NULL
) ON [PRIMARY]GO
CREATE TABLE [dbo].[xiaoliang](
[cpid] [varchar](50) NULL,
[day1] [int] NULL,
[day2] [int] NULL,
[day3] [int] NULL,
[day4] [int] NULL,
[day5] [int] NULL,
[day6] [int] NULL,
[day7] [int] NULL,
[day8] [int] NULL,
[day9] [int] NULL
) ON [PRIMARY]GO2、插入数据/****** Object:  Database [tempdb1]    Script Date: 2013/12/27 9:18:07 ******/
/****** Object:  Tables [kucun],[xiaoliang] ******/
USE [tempdb1]/****** Object:  Table [kucun] Begin ******/
INSERT INTO[kucun]([cpid],[zl])VALUES('1',1000)
INSERT INTO[kucun]([cpid],[zl])VALUES('2',10000)
INSERT INTO[kucun]([cpid],[zl])VALUES('3',100000)
GO
/****** Object:  Table [kucun] End   ******//****** Object:  Table [xiaoliang] Begin ******/
INSERT INTO[xiaoliang]([cpid],[day1],[day2],[day3],[day4],[day5],[day6],[day7],[day8],[day9])VALUES('1',10,20,30,40,50,60,70,80,90)
INSERT INTO[xiaoliang]([cpid],[day1],[day2],[day3],[day4],[day5],[day6],[day7],[day8],[day9])VALUES('2',100,200,300,400,500,600,700,800,900)
INSERT INTO[xiaoliang]([cpid],[day1],[day2],[day3],[day4],[day5],[day6],[day7],[day8],[day9])VALUES('3',10,20,30,40,50,60,70,80,90)
GO
/****** Object:  Table [xiaoliang] End   ******/
3、执行语句select t1.cpid,t1.zl-_day1,t1.zl-_day2,t1.zl-_day3,t1.zl-_day4 from kucun t1 left join
(select cpid,day1 as _day1,day1+day2 as _day2,day1+day2+day3 as _day3,day1+day2+day3+day4 as _day4 from xiaoliang) t2
on t1.cpid=t2.cpid

Answer 3

版主所见正是问题。天数很多了。可能有一年。可否不这样day1，day2的一直写下去？

Answer 4

可以，不过单纯一句SQL写不出来，要点预处理，我试试

Answer 5

是2000还是2005或以上的sqlserver？

Answer 6

按我的水平只能写出这么劣质的脚本了----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-27 10:04:46
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[Sales]
if object_id('[Sales]') is not null drop table [Sales]
go 
create table [Sales]([产品id] int,[Day1] int,[Day2] int,[Day3] int,[Day4] int,[Day5] int,[Day6] int,[Day7] int,[Day8] int,[Day9] int)
insert [Sales]
select 1,10,20,30,40,50,60,70,80,90 union all
select 2,100,200,300,400,500,600,700,800,900 union all
select 3,10,20,30,40,50,60,70,80,90
--> 测试数据：[Store]
if object_id('[Store]') is not null drop table [Store]
go 
create table [Store]([产品id] int,[总量] int)
insert [Store]
select 1,1000 union all
select 2,10000 union all
select 3,100000
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t1','U')IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t','U')IS NOT NULL
DROP TABLE #t
CREATE TABLE #t ([产品id] int,[Day] VARCHAR(5),[value] INT );
declare @s nvarchar(MAX)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('[Sales]') and Name not in('产品id') 
order by Colid
INSERT INTO #t
exec('select [产品id],[Day],[value]  from Sales unpivot ([value] for [day] in('+@s+'))b')
;WITH cte AS 
(SELECT a.*,b.[总量] ,ROW_NUMBER()OVER(PARTITION BY a.产品id ORDER BY [day])oid 
FROM #t a INNER JOIN [Store] b ON a.产品id=b.产品id
),
cte2 AS 
(
SELECT 产品id,[day],CASE WHEN 总量-[value]>0 THEN 总量-[value] ELSE [value] END [value],总量-[value] AS 总量,oid
from cte 
WHERE oid=1
UNION ALL 
SELECT a.产品Id,a.[day],b.总量-a.[value] [value],b.总量-a.[value] 总量,a.oid
FROM cte a INNER JOIN cte2 b ON a.oid=b.oid +1 AND a.产品id=b.产品id
)
SELECT 产品id,[day],[value] INTO #t1
FROM cte2
ORDER BY 产品id; 
declare @s1 nvarchar(max)
Select     @s1=isnull(@s1+',','')+quotename([DAY]) from #t1 group BY [DAY]
exec('select * from #t1 pivot (max([value]) for [day] in('+@s1+'))b')
----------------结果----------------------------
/*
产品id        Day1        Day2        Day3        Day4        Day5        Day6        Day7        Day8        Day9
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           990         970         940         900         850         790         720         640         550
2           9900        9700        9400        9000        8500        7900        7200        6400        5500
3           99990       99970       99940       99900       99850       99790       99720       99640       99550 
*/

Answer 7

也不能算是拙劣吧。这个表结构就是比较清晰容易理解，这是底层设计的原则。这个业务大部分时候做法都是在服务器端程序递归多次连数据库，算出结果。我也没想到版大竟然可以用几行sql搞定。很神奇。

Answer 8

我代码有两层，一层是把你的月份先转成行，这部分就是我觉得比较适合做基础表的格式，然后再用CTE来迭代计算，最后再转回去，如果你的表本身就是产品Id,天,数值 这昂的格式存放，那就好处理多了