create table #t (out_no varchar(10) primary key,date datetime,part varchar(30),qty numeric(12,4),price numeric(12,4)
insert into #t
select 'A01','2009-1-11','B001',100,1.1 union all
select 'A02','2009-1-12','B002',200,1.3 union all
select 'A03','2009-2-22','B003',120,1.5 union all
select 'A04','2009-3-22','B004',155,1.2 union all
select 'A05','2009-4-20','B005',600,1.6 union all
select 'A06','2009-4-22','B006',750,1.6 要求按月份分类汇总得出以下报表
月份 1月 2月 3月 4月 5月
出货数量 300 120 155 1350 0
出货金额 370 180 186 2160 0
解决方案 »
- 触发器
- select top (@index) eid from tbevaluate哪错了?
- 为何我在删除SQL SERVER2000时总是删不掉```因为当想重新安装时总是说``以前安装过```我重启也没有用````反而总是在桌面上出现 WINDOWS I
- 相差一定范围的记录只取一条怎么写SQL
- SQL语句请教
- SQL语句的模糊查询?
- 写了一个sql语句,用到inner join ,提示join附近语法错误
- 如何停掉正在使用的数据库然后在启动它啊!如何停掉正在使用的数据库然后在启动它啊!在线等
- 奇怪的数据库问题
- sqlserver用MSSQLSERVER连接不上
- 有一张表A,有字段Name,Password等,我想得到这张表Name字段长度最长的一个,查询语句怎么写,一直查不出,谢谢
- |zyciis| 在执行插入记录的触发器之前,记录是否已经被插入了吗?
月份='出货数量',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END)
FROM #t
UNION ALL
SELECT
月份='出货金额',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN price ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN price ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN price ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN price ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN price ELSE 0 END)
FROM #t
insert into #t
select 'A01','2009-1-11','B001',100,1.1 union all
select 'A02','2009-1-12','B002',200,1.3 union all
select 'A03','2009-2-22','B003',120,1.5 union all
select 'A04','2009-3-22','B004',155,1.2 union all
select 'A05','2009-4-20','B005',600,1.6 union all
select 'A06','2009-4-22','B006',750,1.6
--
--select * from #tselect
月份='出货数量',
[1月]=sum(case when month(date)=1 then qty else 0 end),
[2月]=sum(case when month(date)=2 then qty else 0 end),
[3月]=sum(case when month(date)=3 then qty else 0 end),
[4月]=sum(case when month(date)=4 then qty else 0 end),
[5月]=sum(case when month(date)=5 then qty else 0 end)
from #t
union all
select
月份='出货金额',
[1月]=sum(case when month(date)=1 then price*qty else 0 end),
[2月]=sum(case when month(date)=2 then price*qty else 0 end),
[3月]=sum(case when month(date)=3 then price*qty else 0 end),
[4月]=sum(case when month(date)=4 then price*qty else 0 end),
[5月]=sum(case when month(date)=5 then price*qty else 0 end)
from #t/*
月份 1月 2月 3月 4月 5月
-------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
出货数量 300.0000 120.0000 155.0000 1350.0000 0.0000
出货金额 370.0000 180.0000 186.0000 2160.0000 0.0000(2 行受影响)
*/drop table #t
insert into #t
select 'A01','2009-1-11','B001',100,1.1 union all
select 'A02','2009-1-12','B002',200,1.3 union all
select 'A03','2009-2-22','B003',120,1.5 union all
select 'A04','2009-3-22','B004',155,1.2 union all
select 'A05','2009-4-20','B005',600,1.6 union all
select 'A06','2009-4-22','B006',750,1.6
/*
月份 1月 2月 3月 4月 5月
出货数量 300 120 155 1350 0
出货金额 370 180 186 2160 0
*/
select * from #t
SELECT
月份='出货数量',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END)
FROM #t
UNION ALL
SELECT
月份='出货金额',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN total ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN total ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN total ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN total ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN total ELSE 0 END)
FROM (select out_no,date,part,qty,price,qty*price total from #t) mdrop table #t
SELECT DATEPART(MM,DATE) MM, SUM(QTY*PRICE) MO INTO #T1 FROM #T GROUP BY DATEPART(MM,DATE)SELECT
SUM(CASE WHEN DATEPART(MM,DATE)=1 THEN QTY ELSE 0 END) AS '1月',
SUM(CASE WHEN DATEPART(MM,DATE)=2 THEN QTY ELSE 0 END) AS '2月',
SUM(CASE WHEN DATEPART(MM,DATE)=3 THEN QTY ELSE 0 END) AS '3月',
SUM(CASE WHEN DATEPART(MM,DATE)=4 THEN QTY ELSE 0 END) AS '4月',
SUM(CASE WHEN DATEPART(MM,DATE)=5 THEN QTY ELSE 0 END) AS '5月'
FROM #TUNION ALLSELECT
MAX(CASE WHEN MM=1 THEN MO ELSE 0 END) AS '1月',
MAX(CASE WHEN MM=2 THEN MO ELSE 0 END) AS '2月',
MAX(CASE WHEN MM=3 THEN MO ELSE 0 END) AS '3月',
MAX(CASE WHEN MM=4 THEN MO ELSE 0 END) AS '4月',
MAX(CASE WHEN MM=5 THEN MO ELSE 0 END) AS '5月'
FROM #T1300.0000 120.0000 155.0000 1350.0000 .0000
370.0000 180.0000 186.0000 2160.0000 .0000
SELECT TOP 1 '日期','1月','2月','3月','4月','5月' FROM #t
UNION ALL
SELECT
'出货数量',
CAST(SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END) AS VARCHAR)
FROM #t
UNION ALL
SELECT
'出货金额',
CAST(SUM(CASE WHEN MONTH(date)=1 THEN total ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=2 THEN total ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=3 THEN total ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=4 THEN total ELSE 0 END) AS VARCHAR),
CAST(SUM(CASE WHEN MONTH(date)=5 THEN total ELSE 0 END) AS VARCHAR)
FROM (select out_no,date,part,qty,price,qty*price total from #t) m