22,207
社区成员
发帖
与我相关
我的任务
分享
;WITH a AS (
SELECT fitemname,
YEAR(fdate) [year],
MONTH(fdate) [month],
CONVERT(varchar(23),fdate,121) fdate,
fqtyin,
fqtyou,
fqtyin-fqtyou fqty
FROM stockin
)
,b AS (
SELECT [year],
[month],
SUM(fqtyin) fqtyin,
SUM(fqtyou) fqtyou,
SUM(fqty) fqty,
GROUPING([month]) g1,
GROUPING([year]) g2
FROM a
GROUP BY [year], [month] WITH ROLLUP
)
,c AS (
SELECT fitemname,
[year],
[month],
fdate,
fqtyin,
fqtyou,
fqty,
0 g0,
0 g1
FROM a
UNION ALL
SELECT '' fitemname,
[year],
[month],
CONVERT(varchar(23),
CASE WHEN g1 = 1 THEN
CONVERT(varchar(4),[year])
ELSE
CONVERT(varchar(4),[year])+'-'+
RIGHT('0'+CONVERT(varchar(4),[month]),2)
END) fdate,
fqtyin,
fqtyou,
fqty,
1 g0,
g1
FROM b
WHERE g2 <> 1
)
SELECT fitemname,
fdate,
CASE WHEN g1 = 1 THEN '本年累计'
WHEN g0 = 1 THEN '本月累计'
ELSE ''
END memo,
fqtyin,
fqtyou,
fqty
FROM c
ORDER BY g1,[month],g0
fitemname fdate memo fqtyin fqtyou fqty
---------- ----------------------- -------- ---------- ---------- ----------
AAA 2003-01-03 00:00:00.000 125.00 20.00 105.00
AAA 2003-01-10 00:00:00.000 120.00 20.00 100.00
BBB 2003-01-13 00:00:00.000 -20.00 20.00 -40.00
AAA 2003-01-30 00:00:00.000 25.00 20.00 5.00
2003-01 本月累计 250.00 80.00 170.00
BBB 2003-02-03 00:00:00.000 100.00 20.00 80.00
2003-02 本月累计 100.00 20.00 80.00
CCC 2003-03-05 00:00:00.000 120.00 20.00 100.00
2003-03 本月累计 120.00 20.00 100.00
2003 本年累计 470.00 120.00 350.00
;WITH a AS (
SELECT fitemname,
YEAR(fdate) [year],
MONTH(fdate) [month],
CONVERT(varchar(23),fdate,121) fdate,
fqtyin,
fqtyou,
fqtyin-fqtyou fqty
FROM stockin
)
,b AS (
SELECT fitemname,
[year],
[month],
SUM(fqtyin) fqtyin,
SUM(fqtyou) fqtyou,
SUM(fqty) fqty,
GROUPING([month]) g1,
GROUPING([year]) g2,
GROUPING(fitemname) g3
FROM a
GROUP BY fitemname, [year], [month] WITH ROLLUP
)
,c AS (
SELECT fitemname,
[year],
[month],
fdate,
fqtyin,
fqtyou,
fqty,
0 g0,
0 g1
FROM a
UNION ALL
SELECT fitemname,
[year],
[month],
CONVERT(varchar(23),
CASE WHEN g1 = 1 THEN
CONVERT(varchar(4),[year])
ELSE
CONVERT(varchar(4),[year])+'-'+
RIGHT('0'+CONVERT(varchar(4),[month]),2)
END) fdate,
fqtyin,
fqtyou,
fqty,
1 g0,
g1
FROM b
WHERE g2 <> 1
AND g3 <> 1
)
SELECT fitemname,
fdate,
CASE WHEN g1 = 1 THEN '本年累计'
WHEN g0 = 1 THEN '本月累计'
ELSE ''
END memo,
fqtyin,
fqtyou,
fqty
FROM c
ORDER BY fitemname,g1,[month],g0
fitemname fdate memo fqtyin fqtyou fqty
---------- ----------------------- -------- ---------- ---------- ----------
AAA 2003-01-03 00:00:00.000 125.00 20.00 105.00
AAA 2003-01-10 00:00:00.000 120.00 20.00 100.00
AAA 2003-01-30 00:00:00.000 25.00 20.00 5.00
AAA 2003-01 本月累计 270.00 60.00 210.00
AAA 2003 本年累计 270.00 60.00 210.00
BBB 2003-01-13 00:00:00.000 -20.00 20.00 -40.00
BBB 2003-01 本月累计 -20.00 20.00 -40.00
BBB 2003-02-03 00:00:00.000 100.00 20.00 80.00
BBB 2003-02 本月累计 100.00 20.00 80.00
BBB 2003 本年累计 80.00 40.00 40.00
CCC 2003-03-05 00:00:00.000 120.00 20.00 100.00
CCC 2003-03 本月累计 120.00 20.00 100.00
CCC 2003 本年累计 120.00 20.00 100.00
select convert(varchar(7), 日期, 121) 年月, SUM(收入), SUM(付出) from 表 group by convert(varchar(7), 日期, 121)
本年合计,类似下面的语句:
select a.年月, SUM(b.收入) 收入, SUM(b.付出) 付出 from
(select convert(varchar(7), 日期, 121) 年月, SUM(收入) 收入, SUM(付出) 付出 from 表 group by convert(varchar(7), 日期, 121)) a
left join
(select convert(varchar(7), 日期, 121) 年月, SUM(收入) 收入, SUM(付出) 付出 from 表 group by convert(varchar(7), 日期, 121)) b
on a.年月>=b.年月 and SUBSTRING(a.年月,1,4)=SUBSTRING(b.年月,1,4)
group by a.年月
将年月转换成当月最后一天的最后一秒,网上可以找到。