uj5u.com熱心網友回復：
先給大神們提供一個測驗資料
CREATE TABLE #A(月份 VARCHAR(20),期初 int,入庫 int,出庫 int,結存 int)
INSERT INTO #A values('2020-01',0,18728,6195,12533)
INSERT INTO #A values('2020-02',12533,-434,11260,839)
INSERT INTO #A values('2020-03',839,17900,15605,3134)
INSERT INTO #A values('2020-04',3134,7112,1929,8317)
INSERT INTO #A values('2020-05',8317,0,7509,809)
INSERT INTO #A values('2020-06',809,19989,20212,586)
INSERT INTO #A values('2020-07',586,23188,8969,14804)
INSERT INTO #A values('2020-08',14804,12404,22320,4889)
INSERT INTO #A values('2020-09',4889,41084,35512,10461)
INSERT INTO #A values('2020-10',10461,33684,36669,7476)
uj5u.com熱心網友回復：
INSERT INTO #A values('2020-05',8317,0,7509,809)
INSERT INTO #A values('2020-06',809,19989,20212,586)
INSERT INTO #A values('2020-07',586,23188,8969,14804)
INSERT INTO #A values('2020-08',14804,12404,22320,4889)
INSERT INTO #A values('2020-09',4889,41084,35512,10461)
INSERT INTO #A values('2020-10',10461,33684,36669,7476)
declare @time1 varchar(20),@time2 varchar(20)
set @time1='2020-03'
set @time2='2020-07'
select
@time1+'-'+@time2 期間,
(select a1.期初 from #A a1 where CONVERT(date,a1.月份+'-01')=DATEADD(MM,-1,CONVERT(date,@time1+'-01'))) 期初,
SUM(a.入庫) 入庫,
SUM(a.出庫) 出庫,
(select a1.結存 from #A a1 where a1.月份=@time2) 結存
from #A a
where CONVERT(date,a.月份+'-01') between DATEADD(MM,-1,CONVERT(date,@time1+'-01')) and CONVERT(date,@time2+'-01')
所以'2020-03'和'2020-07'是變數嗎，不過為什么話說3到7月的連2月的也算上了
uj5u.com熱心網友回復：
CREATE TABLE #A(月份 VARCHAR(20),期初 int,入庫 int,出庫 int,結存 int)
INSERT INTO #A values('2020-01',0,18728,6195,12533)
INSERT INTO #A values('2020-02',12533,-434,11260,839)
INSERT INTO #A values('2020-03',839,17900,15605,3134)
INSERT INTO #A values('2020-04',3134,7112,1929,8317)
INSERT INTO #A values('2020-05',8317,0,7509,809)
INSERT INTO #A values('2020-06',809,19989,20212,586)
INSERT INTO #A values('2020-07',586,23188,8969,14804)
INSERT INTO #A values('2020-08',14804,12404,22320,4889)
INSERT INTO #A values('2020-09',4889,41084,35512,10461)
INSERT INTO #A values('2020-10',10461,33684,36669,7476)
select
q1.[月份]
,q1.[期初]
,( select sum([入庫]) from #A where cast([月份]+'-01' as datetime) between cast(q1.[月份]+'-01' as datetime) and cast('2020-07'+'-01' as datetime)) as [入庫]
,( select sum([出庫]) from #A where cast([月份]+'-01' as datetime) between cast(q1.[月份]+'-01' as datetime) and cast('2020-07'+'-01' as datetime)) as [出庫]
,(select [結存] from #A q1 where [月份]='2020-07') as [結存]
from #A q1 where [月份]='2020-02'
uj5u.com熱心網友回復：
忘記千分位逗號了
select a.月份,
case when a.期初=0 then '-' else REPLACE(convert(varchar,cast(a.期初 as money),1),'.00','') end 期初,
case when a.入庫=0 then '-' else REPLACE(convert(varchar,cast(a.入庫 as money),1),'.00','') end 入庫,
case when a.出庫=0 then '-' else REPLACE(convert(varchar,cast(a.出庫 as money),1),'.00','') end 出庫,
case when a.結存=0 then '-' else REPLACE(convert(varchar,cast(a.結存 as money),1),'.00','') end 結存
into #B
from #A a
select a.月份,
case when a.期初='' then 0 else convert(int,REPLACE(a.期初,',','')) end 期初,
case when a.入庫='' then 0 else convert(int,REPLACE(a.入庫,',','')) end 入庫,
case when a.出庫='' then 0 else convert(int,REPLACE(a.出庫,',','')) end 出庫,
case when a.結存='' then 0 else convert(int,REPLACE(a.結存,',','')) end 結存
into #C
from #B a
uj5u.com熱心網友回復：
不好意思，搞錯了，應該要取 2020-02'至'2020-07'的資料。uj5u.com熱心網友回復：
更正如下：謝謝
uj5u.com熱心網友回復：
頂一下。
uj5u.com熱心網友回復：
declare @time1 varchar(20),@time2 varchar(20)
set @time1='2020-02'
set @time2='2020-07'
select
@time1+'-'+@time2 期間,
(select a1.期初 from #A a1 where a1.月份=@time1) 期初,
SUM(a.入庫) 入庫,
SUM(a.出庫) 出庫,
(select a1.結存 from #A a1 where a1.月份=@time2) 結存
from #A a
where CONVERT(date,a.月份+'-01') between CONVERT(date,@time1+'-01') and CONVERT(date,@time2+'-01')
uj5u.com熱心網友回復：
你可以把一個表當三個表用，第一個表查期初，第二個表查累計出入庫，第三個表查結存，按IDD NAME 放在一起就OK了select a.IDD,a.Name,'2020-02-2020-07' as 期間, a.期初,b.出庫,b.入庫,c.結存
from tb a
join (select idd,name,sum() as 入庫,sum() as 出庫 from tb where 月份 between ... and ... group by idd,name) as b
on a.idd = b.idd and a.name = b.name
join (select idd,name,結存 from tb where 月份 = '...') c
on a.idd = c.idd and a.name = c.name
where a.idd = ... and a.name = ... and a.月份 = '...'
類似，條件自己填入
uj5u.com熱心網友回復：
--drop table #A
CREATE TABLE #A(ID VARCHAR(50),月份 VARCHAR(20),期初 int,入庫 int,出庫 int,結存 int)
INSERT INTO #A values('A','2020-01',0,18728,6195,12533)
INSERT INTO #A values('A','2020-02',12533,-434,11260,839)
INSERT INTO #A values('A','2020-03',839,17900,15605,3134)
INSERT INTO #A values('A','2020-04',3134,7112,1929,8317)
INSERT INTO #A values('A','2020-05',8317,0,7509,809)
INSERT INTO #A values('A','2020-06',809,19989,20212,586)
INSERT INTO #A values('A','2020-07',586,23188,8969,14804)
INSERT INTO #A values('A','2020-08',14804,12404,22320,4889)
INSERT INTO #A values('A','2020-09',4889,41084,35512,10461)
INSERT INTO #A values('A','2020-10',10461,33684,36669,7476)
declare @BeginDate varchar(20),@EndDate varchar(20)
set @BeginDate='2020-02'
set @EndDate='2020-07'
SELECT distinct '期初' = (SELECT [結存] FROM #A WHERE 月份=@BeginDate AND A.ID=#A.ID),
'入庫' = (SELECT sum([入庫]) FROM #A WHERE 月份 between @BeginDate and @EndDate
AND A.ID=#A.ID),
'出庫' = (SELECT sum([出庫]) FROM #A WHERE 月份 between @BeginDate and @EndDate
AND A.ID=#A.ID),
'結存' = (SELECT [結存] FROM #A WHERE 月份=@EndDate AND A.ID=#A.ID)
FROM #A A
/*
期初 入庫 出庫 結存
839 67755 65484 14804
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/232789.html
標籤：應用實例
上一篇：請教如何寫動態欄位的拼接陳述句
下一篇：好久沒來，聯絡聯絡老朋友