问
数据表Mytable
WorkID Name SaleDate DayCount
001 张三 2008-01-01 3
002 李四 2008-01-10 5
001 张三 2008-01-20 1
003 老王 2008-01-20 2
001 张三 2008-04-01 6
002 李四 2008-04-02 7
004 小强 2008-04-03 8
要求按月输出明细:
2008-01明细:
WorkID Name 2008-01-01 2008-01-02 2008-01-03 2008-01-04 ...... 2008-01-31
001 张三 3 0 0 0 0
002 李四 0 0 0 0 0
003 老王 0 0 0 0 0
004 小强 0 0 0 0 0 答:创建存储过程,动态生成SQL即可create proc p_test @date varchar(10)
as
declare @sql varchar(8000)
declare @i int
select @sql='select WorkID,Name',@i=1
while 1=1
begin
select @sql=@sql+',sum(case when convert(char(10),SaleDate,120)='''+(@date+'-'+right('00'+ltrim(@i),2))+''' then DayCount else 0 end) ['+(@date+'-'+right('00'+ltrim(@i),2))+']',
@i=@i+1
if @i>day(dateadd(day,-1,dateadd(month,1,@date+'-1'))) break
end
exec (@sql+' from tb group by WorkID,Name order by WorkID')
go
exec p_test '2008-04'
数据表Mytable
WorkID Name SaleDate DayCount
001 张三 2008-01-01 3
002 李四 2008-01-10 5
001 张三 2008-01-20 1
003 老王 2008-01-20 2
001 张三 2008-04-01 6
002 李四 2008-04-02 7
004 小强 2008-04-03 8
要求按月输出明细:
2008-01明细:
WorkID Name 2008-01-01 2008-01-02 2008-01-03 2008-01-04 ...... 2008-01-31
001 张三 3 0 0 0 0
002 李四 0 0 0 0 0
003 老王 0 0 0 0 0
004 小强 0 0 0 0 0 答:创建存储过程,动态生成SQL即可create proc p_test @date varchar(10)
as
declare @sql varchar(8000)
declare @i int
select @sql='select WorkID,Name',@i=1
while 1=1
begin
select @sql=@sql+',sum(case when convert(char(10),SaleDate,120)='''+(@date+'-'+right('00'+ltrim(@i),2))+''' then DayCount else 0 end) ['+(@date+'-'+right('00'+ltrim(@i),2))+']',
@i=@i+1
if @i>day(dateadd(day,-1,dateadd(month,1,@date+'-1'))) break
end
exec (@sql+' from tb group by WorkID,Name order by WorkID')
go
exec p_test '2008-04'
解决方案 »
- 关于筛选重复值然后统计的问题
- 如何将2006-10-20 17:04:23转换为Oct/20/2006?
- sqlserver中有没有办法将表名当作变量,例如@tablename select* from @tablename
- select *问题(急,在线等)
- 数据库时间字段的取出的问题/
- 求一个触发器:如果插入的数据在表中已经存在,则取消插入
- 请推荐几本关于数据库的好书
- 在建视图的时候可不可以for 语句来实现循环功能?
- 存储过程与用户问题
- 怎樣使得 Insert﹑Update﹑Delete 等SQL語句執行時﹐不會產生 Log 記錄
- 过滤HTML标签的SQL查询
- 求sqlserver查询某个时间段的查询方法
drop table tb
go
create table tb(id int,date datetime,sale decimal(10,2))
insert into tb select 1,'2008-01-01',10
insert into tb select 2,'2008-01-01',10
insert into tb select 3,'2008-02-01',20
insert into tb select 4,'2008-02-01',20
insert into tb select 5,'2008-03-01',30
insert into tb select 6,'2008-03-01',30
insert into tb select 7,'2008-04-01',40
insert into tb select 8,'2008-04-01',40
insert into tb select 9,'2008-05-01',50
insert into tb select 10,'2008-05-01',50
insert into tb select 11,'2008-06-01',60
insert into tb select 12,'2008-06-01',60
insert into tb select 13,'2008-07-01',70
insert into tb select 14,'2008-07-01',70
insert into tb select 15,'2008-08-01',80
insert into tb select 16,'2008-08-01',80
insert into tb select 17,'2008-09-01',90
insert into tb select 18,'2008-09-01',90
insert into tb select 19,'2008-10-01',100
insert into tb select 20,'2008-10-01',100
insert into tb select 21,'2008-11-01',110
insert into tb select 22,'2008-11-01',110
insert into tb select 23,'2008-12-01',120
insert into tb select 24,'2008-12-01',120
insert into tb select 25,'2009-01-01',10
--动态查询
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'sum(case when datepart(mm,date)='''+ltrim([month])+''' then sale else 0 end) as ['+ltrim([month])+'月]'
from (select distinct datepart(mm,date) as [month] from tb)a
exec('select datename(yy,date) as yy,'+@sql+' from tb group by datename(yy,date)')
yy 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
2008 20.00 40.00 60.00 80.00 100.00 120.00 140.00 160.00 180.00 200.00 220.00 240.00
2009 10.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
没有的月份就不列出来
if object_id('TB') is not null drop table TB
go
create table TB
(年月 INT,品番 INT,营业额 INT
)
INSERT INTO TB SELECT 200801 , 1001 , 1000
UNION ALL SELECT 200801 , 1002 , 1500
UNION ALL SELECT 200801 , 1003 , 1300
UNION ALL SELECT 200802 , 1001 , 1100
UNION ALL SELECT 200802 , 1002 , 1600
UNION ALL SELECT 200802 , 1003 , 1400
UNION ALL SELECT 200803 , 1001 , 1000
UNION ALL SELECT 200803 , 1002 , 1500
UNION ALL SELECT 200803 , 1003 , 1300
UNION ALL SELECT 200804 , 1001 , 1000
UNION ALL SELECT 200804 , 1002 , 1500
UNION ALL SELECT 200804 , 1003 , 1300 DECLARE @SQL1 VARCHAR(8000),@I INT
SELECT @SQL1='',@I= MAX(RIGHT(年月,2)) FROM TBWHILE @I>0
SELECT @SQL1=@SQL1+ ','+QUOTENAME(CONVERT(VARCHAR,@I)+'月')+'=SUM(CASE WHEN RIGHT(年月,2)='+CONVERT(VARCHAR,@I)+' THEN 营业额 END)',@I=@I-1
EXEC('
SELECT LEFT(年月,4) AS 年,
品番'+@SQL1+'
,总计=SUM(营业额)
FROM TB
GROUP BY LEFT(年月,4),品番')/*
年 品番 4月 3月 2月 1月 总计
-------- ----------- ----------- ----------- ----------- ----------- -----------
2008 1001 1000 1000 1100 1000 4100
2008 1002 1500 1500 1600 1500 6100
2008 1003 1300 1300 1400 1300 5300
*/
if object_id('TB') is not null drop table TB
go
create table TB
(年月 INT,品番 INT,营业额 INT
)
INSERT INTO TB SELECT 200801 , 1001 , 1000
UNION ALL SELECT 200801 , 1002 , 1500
UNION ALL SELECT 200801 , 1003 , 1300
UNION ALL SELECT 200802 , 1001 , 1100
UNION ALL SELECT 200802 , 1002 , 1600
UNION ALL SELECT 200802 , 1003 , 1400
UNION ALL SELECT 200803 , 1001 , 1000
UNION ALL SELECT 200803 , 1002 , 1500
UNION ALL SELECT 200803 , 1003 , 1300
UNION ALL SELECT 200804 , 1001 , 1000
UNION ALL SELECT 200804 , 1002 , 1500
UNION ALL SELECT 200804 , 1003 , 1300 DECLARE @SQL1 VARCHAR(8000),@I INT
SELECT @SQL1='',@I=1WHILE @I<=12
SELECT @SQL1=@SQL1+ ','+QUOTENAME(CONVERT(VARCHAR,@I)+'月')+
'=ISNULL(SUM(CASE WHEN RIGHT(年月,2)='+CONVERT(VARCHAR,@I)+' THEN 营业额 END),0)',@I=@I+1
EXEC('
SELECT LEFT(年月,4) AS 年,
品番'+@SQL1+'
,总计=SUM(营业额)
FROM TB
GROUP BY LEFT(年月,4),品番')
/*
年 品番 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 总计
-------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008 1001 1000 1100 1000 1000 0 0 0 0 0 0 0 0 4100
2008 1002 1500 1600 1500 1500 0 0 0 0 0 0 0 0 6100
2008 1003 1300 1400 1300 1300 0 0 0 0 0 0 0 0 5300*/