进销存很难的SQL语句

Question

select *,t=Price*Num from A order by [ID],IODATE

Answer 1

select *,t=Price*Num from A order by [ID],IODATE/*IODATE     ID   Kind       Price                Num         t                               
---------- ---- ---------- -------------------- ----------- ------------------------------- 
2005-10-01 000  01         10.00                100         1000.00
2005-10-03 000  02         10.00                80          800.00
2005-10-09 000  01         10.00                300         3000.00
2005-10-16 000  02         10.00                250         2500.00
2005-10-20 000  01         10.00                10          100.00
2005-10-25 000  01         10.00                40          400.00
2005-10-01 001  01         10.00                100         1000.00
2005-10-03 001  02         10.00                80          800.00
2005-10-09 001  01         10.00                300         3000.00
2005-10-16 001  01         10.00                250         2500.00
2005-10-20 001  01         10.00                10          100.00
2005-10-25 001  01         10.00                200         2000.00（所影响的行数为 12 行）
*/

Answer 2

;WITH CTE AS
(
SELECT *,CASE WHEN Kind='01' THEN Price*Num ELSE -1*Price*Num END AS TOTAL
FROM (SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY IODATE ORDER BY GETDATE()) FROM A) T
)
SELECT IODATE,ID,Kind,TOTAL=(SELECT SUM(TOTAL) FROM CTE WHERE SEQ=T.SEQ AND IODATE<=T.IODATE)
FROM CTE AS T
ORDER BY ID,IODATE
/*
IODATE     ID   Kind       TOTAL
---------- ---- ---------- ---------------------------------------
2005-10-01 000  01         1000.00
2005-10-03 000  02         200.00
2005-10-09 000  01         3200.00
2005-10-16 000  02         700.00
2005-10-20 000  01         800.00
2005-10-25 000  01         1200.00
2005-10-01 001  01         1000.00
2005-10-03 001  02         200.00
2005-10-09 001  01         3200.00
2005-10-16 001  01         5700.00
2005-10-20 001  01         5800.00
2005-10-25 001  01         7800.00
*/

Answer 3

-------------------------------------------
--  Author: liangCK 小梁 & angellan 兰儿
--  Commen: 小梁 爱 兰儿
--  Date  : 2009-05-27 17:35:58
-------------------------------------------
 
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (IODATE DATETIME,ID VARCHAR(3),Kind VARCHAR(2),Price NUMERIC(4,2),Num INT)
INSERT INTO #tb
SELECT '2005-10-01','000','01',10.00,100 UNION ALL
SELECT '2005-10-03','000','02',10.00,80 UNION ALL
SELECT '2005-10-09','000','01',10.00,300 UNION ALL
SELECT '2005-10-16','000','02',10.00,250 UNION ALL
SELECT '2005-10-20','000','01',10.00,10 UNION ALL
SELECT '2005-10-25','000','01',10.00,40 UNION ALL
SELECT '2005-10-01','001','01',10.00,100 UNION ALL
SELECT '2005-10-03','001','02',10.00,80 UNION ALL
SELECT '2005-10-09','001','01',10.00,300 UNION ALL
SELECT '2005-10-16','001','01',10.00,250 UNION ALL
SELECT '2005-10-20','001','01',10.00,10 UNION ALL
SELECT '2005-10-25','001','01',10.00,200--SQL查询如下:DECLARE @t TABLE(IODATE DATETIME,ID VARCHAR(3),
                 Kind VARCHAR(2),Price NUMERIC(4,2),Num INT,
                 金额变化 NUMERIC(19,2));INSERT @t SELECT *,0 FROM #tb;DECLARE @money NUMERIC(19,2);
SET @money = 0;UPDATE @t SET
    @money = ABS(CASE WHEN Kind = '01' THEN Price * Num + @money
                   ELSE Price * Num - @money END),
    金额变化 = @money;SELECT * FROM @t;

Answer 4

set nocount on if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[A] GO CREATE TABLE [dbo].[A] ( [IODATE] [char] (10) NOT NULL , --日期 [ID]  [char] (3)  NOT NULL , --产品编号 [Kind] [char] (10) NOT NULL ,  --类别 '01' 入库 '02' 出库 [Price] [decimal](18, 2) NOT NULL , --单价 [Num] [int] NOT NULL  --数量 ) ON [PRIMARY] GO INSERT INTO A SELECT '2005-10-01','000','01',10.0,100 INSERT INTO A SELECT '2005-10-03','000','02',10.0,80 INSERT INTO A SELECT '2005-10-09','000','01',10.0,300 INSERT INTO A SELECT '2005-10-16','000','02',10.0,250 INSERT INTO A SELECT '2005-10-20','000','01',10.0,10 INSERT INTO A SELECT '2005-10-25','000','01',10.0,40 INSERT INTO A SELECT '2005-10-01','001','01',10.0,100 INSERT INTO A SELECT '2005-10-03','001','02',10.0,80 INSERT INTO A SELECT '2005-10-09','001','01',10.0,300 INSERT INTO A SELECT '2005-10-16','001','01',10.0,250 INSERT INTO A SELECT '2005-10-20','001','01',10.0,10 INSERT INTO A SELECT '2005-10-25','001','01',10.0,200 
select * from A
select *,px=identity(int) into # from a
--select * from #
select *,金额变化=(select sum(case when kind='01' then price*num else -price*num end) from # where px<=t.px)  from # tdrop table #,a
/*
IODATE     ID   Kind       Price                                   Num         px          金额变化
---------- ---- ---------- --------------------------------------- ----------- ----------- ---------------------------------------
2005-10-01 000  01         10.00                                   100         1           1000.00
2005-10-03 000  02         10.00                                   80          2           200.00
2005-10-09 000  01         10.00                                   300         3           3200.00
2005-10-16 000  02         10.00                                   250         4           700.00
2005-10-20 000  01         10.00                                   10          5           800.00
2005-10-25 000  01         10.00                                   40          6           1200.00
2005-10-01 001  01         10.00                                   100         7           2200.00
2005-10-03 001  02         10.00                                   80          8           1400.00
2005-10-09 001  01         10.00                                   300         9           4400.00
2005-10-16 001  01         10.00                                   250         10          6900.00
2005-10-20 001  01         10.00                                   10          11          7000.00
2005-10-25 001  01         10.00                                   200         12          9000.00
*/

Answer 5

-------------------------------------------
--  Author: liangCK 小梁 & angellan 兰儿
--  Commen: 小梁 爱 兰儿
--  Date  : 2009-05-27 17:35:58
-------------------------------------------
 
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (IODATE DATETIME,ID VARCHAR(3),Kind VARCHAR(2),Price NUMERIC(4,2),Num INT)
INSERT INTO #tb
SELECT '2005-10-01','000','01',10.00,100 UNION ALL
SELECT '2005-10-03','000','02',10.00,80 UNION ALL
SELECT '2005-10-09','000','01',10.00,300 UNION ALL
SELECT '2005-10-16','000','02',10.00,250 UNION ALL
SELECT '2005-10-20','000','01',10.00,10 UNION ALL
SELECT '2005-10-25','000','01',10.00,40 UNION ALL
SELECT '2005-10-01','001','01',10.00,100 UNION ALL
SELECT '2005-10-03','001','02',10.00,80 UNION ALL
SELECT '2005-10-09','001','01',10.00,300 UNION ALL
SELECT '2005-10-16','001','01',10.00,250 UNION ALL
SELECT '2005-10-20','001','01',10.00,10 UNION ALL
SELECT '2005-10-25','001','01',10.00,200--SQL查询如下:DECLARE @t TABLE(IODATE DATETIME,ID VARCHAR(3),
                 Kind VARCHAR(2),Price NUMERIC(4,2),Num INT,
                 金额变化 NUMERIC(19,2));INSERT @t SELECT *,0 FROM #tb ORDER BY ID,IODATE;DECLARE @money NUMERIC(19,2);
SET @money = 0;UPDATE @t SET
    @money = ABS(CASE WHEN Kind = '01' THEN Price * Num + @money
                   ELSE Price * Num - @money END),
    金额变化 = @money;SELECT * FROM @t;/*
IODATE                  ID   Kind Price                                   Num         金额变化
----------------------- ---- ---- --------------------------------------- ----------- ---------------------------------------
2005-10-01 00:00:00.000 000  01   10.00                                   100         1000.00
2005-10-03 00:00:00.000 000  02   10.00                                   80          200.00
2005-10-09 00:00:00.000 000  01   10.00                                   300         3200.00
2005-10-16 00:00:00.000 000  02   10.00                                   250         700.00
2005-10-20 00:00:00.000 000  01   10.00                                   10          800.00
2005-10-25 00:00:00.000 000  01   10.00                                   40          1200.00
2005-10-01 00:00:00.000 001  01   10.00                                   100         2200.00
2005-10-03 00:00:00.000 001  02   10.00                                   80          1400.00
2005-10-09 00:00:00.000 001  01   10.00                                   300         4400.00
2005-10-16 00:00:00.000 001  01   10.00                                   250         6900.00
2005-10-20 00:00:00.000 001  01   10.00                                   10          7000.00
2005-10-25 00:00:00.000 001  01   10.00                                   200         9000.00
*/

Answer 6

;WITH CTE AS
(
SELECT *,CASE WHEN Kind='01' THEN Price*Num ELSE -1*Price*Num END AS TOTAL FROM A
)
SELECT IODATE,ID,Kind,Price,Num,TOTAL=(SELECT SUM(TOTAL) FROM CTE WHERE ID=T.ID AND IODATE<=T.IODATE)
FROM CTE AS T
ORDER BY ID,IODATE

Answer 7

SQL2000:
set nocount on if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[A] GO CREATE TABLE [dbo].[A] ( [IODATE] [char] (10) NOT NULL , --日期 [ID]  [char] (3)  NOT NULL , --产品编号 [Kind] [char] (10) NOT NULL ,  --类别 '01' 入库 '02' 出库 [Price] [decimal](18, 2) NOT NULL , --单价 [Num] [int] NOT NULL  --数量 ) ON [PRIMARY] GO INSERT INTO A SELECT '2005-10-01','000','01',10.0,100 INSERT INTO A SELECT '2005-10-03','000','02',10.0,80 INSERT INTO A SELECT '2005-10-09','000','01',10.0,300 INSERT INTO A SELECT '2005-10-16','000','02',10.0,250 INSERT INTO A SELECT '2005-10-20','000','01',10.0,10 INSERT INTO A SELECT '2005-10-25','000','01',10.0,40 INSERT INTO A SELECT '2005-10-01','001','01',10.0,100 INSERT INTO A SELECT '2005-10-03','001','02',10.0,80 INSERT INTO A SELECT '2005-10-09','001','01',10.0,300 INSERT INTO A SELECT '2005-10-16','001','01',10.0,250 INSERT INTO A SELECT '2005-10-20','001','01',10.0,10 INSERT INTO A SELECT '2005-10-25','001','01',10.0,200 
select *,px=identity(int) into # from a order by id,[IODATE]
select *,px1=(select count(1) from # where IODATE=t.IODATE and px<=t.px) into #1 from # t
--select * from #1
select *,金额变化=(select sum(case when kind='01' then price*num else -price*num end) from #1 where IODATE<=T.IODATE and px1=t.px1)  from #1 tdrop table #,#1,a
/*
IODATE     ID   Kind       Price                                   Num         px          px1         金额变化
---------- ---- ---------- --------------------------------------- ----------- ----------- ----------- ---------------------------------------
2005-10-01 000  01         10.00                                   100         1           1           1000.00
2005-10-03 000  02         10.00                                   80          2           1           200.00
2005-10-09 000  01         10.00                                   300         3           1           3200.00
2005-10-16 000  02         10.00                                   250         4           1           700.00
2005-10-20 000  01         10.00                                   10          5           1           800.00
2005-10-25 000  01         10.00                                   40          6           1           1200.00
2005-10-01 001  01         10.00                                   100         7           2           1000.00
2005-10-03 001  02         10.00                                   80          8           2           200.00
2005-10-09 001  01         10.00                                   300         9           2           3200.00
2005-10-16 001  01         10.00                                   250         10          2           5700.00
2005-10-20 001  01         10.00                                   10          11          2           5800.00
2005-10-25 001  01         10.00                                   200         12          2           7800.00
*/

Answer 8

SELECT *,PX=IDENTITY(INT) INTO # FROM A ORDER BY ID,[IODATE]
SELECT *,PX1=(SELECT COUNT(1) FROM # WHERE IODATE=T.IODATE AND PX<=T.PX) INTO #1 FROM # T
--SELECT * FROM #1
SELECT IODATE,ID,KIND,PRICE,NUM,金额变化=(SELECT SUM(CASE WHEN KIND='01' THEN PRICE*NUM ELSE -PRICE*NUM END) FROM #1 WHERE IODATE<=T.IODATE AND PX1=T.PX1)  FROM #1 TDROP TABLE #,#1,A
/*
IODATE     ID   KIND       PRICE                                   NUM         金额变化
---------- ---- ---------- --------------------------------------- ----------- ---------------------------------------
2005-10-01 000  01         10.00                                   100         1000.00
2005-10-03 000  02         10.00                                   80          200.00
2005-10-09 000  01         10.00                                   300         3200.00
2005-10-16 000  02         10.00                                   250         700.00
2005-10-20 000  01         10.00                                   10          800.00
2005-10-25 000  01         10.00                                   40          1200.00
2005-10-01 001  01         10.00                                   100         1000.00
2005-10-03 001  02         10.00                                   80          200.00
2005-10-09 001  01         10.00                                   300         3200.00
2005-10-16 001  01         10.00                                   250         5700.00
2005-10-20 001  01         10.00                                   10          5800.00
2005-10-25 001  01         10.00                                   200         7800.00
*/MODIFY

Answer 9

-------------------------------------------
--  Author: liangCK 小梁 & angellan 兰儿
--  Commen: 小梁 爱 兰儿
--  Date  : 2009-05-27 17:35:58
-------------------------------------------
 
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (IODATE DATETIME,ID VARCHAR(3),Kind VARCHAR(2),Price NUMERIC(4,2),Num INT)
INSERT INTO #tb
SELECT '2005-10-01','000','01',10.00,100 UNION ALL
SELECT '2005-10-03','000','02',10.00,80 UNION ALL
SELECT '2005-10-09','000','01',10.00,300 UNION ALL
SELECT '2005-10-16','000','02',10.00,250 UNION ALL
SELECT '2005-10-20','000','01',10.00,10 UNION ALL
SELECT '2005-10-25','000','01',10.00,40 UNION ALL
SELECT '2005-10-01','001','01',10.00,100 UNION ALL
SELECT '2005-10-03','001','02',10.00,80 UNION ALL
SELECT '2005-10-09','001','01',10.00,300 UNION ALL
SELECT '2005-10-16','001','01',10.00,250 UNION ALL
SELECT '2005-10-20','001','01',10.00,10 UNION ALL
SELECT '2005-10-25','001','01',10.00,200--SQL查询如下:DECLARE @t TABLE(IODATE DATETIME,ID VARCHAR(3),
                 Kind VARCHAR(2),Price NUMERIC(4,2),Num INT,
                 金额变化 NUMERIC(19,2));INSERT @t SELECT *,0 FROM #tb ORDER BY ID,IODATE;DECLARE @money NUMERIC(19,2);
DECLARE @ID VARCHAR(3)
SET @money = 0;UPDATE @t SET
    @money = CASE WHEN @ID=ID THEN 
                     ABS(CASE WHEN Kind = '01' THEN Price * Num + @money
                            ELSE Price * Num - @money END)
                  ELSE Price * Num  END,
    金额变化 = @money,
    @ID = ID;SELECT * FROM @t;/*
IODATE                  ID   Kind Price                                   Num         金额变化
----------------------- ---- ---- --------------------------------------- ----------- ---------------------------------------
2005-10-01 00:00:00.000 000  01   10.00                                   100         1000.00
2005-10-03 00:00:00.000 000  02   10.00                                   80          200.00
2005-10-09 00:00:00.000 000  01   10.00                                   300         3200.00
2005-10-16 00:00:00.000 000  02   10.00                                   250         700.00
2005-10-20 00:00:00.000 000  01   10.00                                   10          800.00
2005-10-25 00:00:00.000 000  01   10.00                                   40          1200.00
2005-10-01 00:00:00.000 001  01   10.00                                   100         1000.00
2005-10-03 00:00:00.000 001  02   10.00                                   80          200.00
2005-10-09 00:00:00.000 001  01   10.00                                   300         3200.00
2005-10-16 00:00:00.000 001  01   10.00                                   250         5700.00
2005-10-20 00:00:00.000 001  01   10.00                                   10          5800.00
2005-10-25 00:00:00.000 001  01   10.00                                   200         7800.00
*/

Answer 10

SELECT IODATE,ID,Kind,Price,Num,
TOTAL=(SELECT SUM(CASE WHEN Kind='01' THEN Price*Num ELSE -1*Price*Num END) FROM A WHERE ID=T.ID AND IODATE<=T.IODATE)
FROM A AS T
ORDER BY ID,IODATE