请教按月份上、中、下旬统计多个字段

kielin 2014-11-20 06:27:27
原始数据有ID,Name,Total1,Total2,Date1字段，如下面显示
 

ID	Name	Total1	        Total2	Date1

1	Name1	8.981132	         476	6/15/2014

1	Name2	9	         18	7/9/2014

1	Name3	14.589062	1203.16	7/25/2014

1	Name4	30.750759	2023.4	7/11/2014

2	Name1	7	        7	6/13/2014

2	Name2	13.491984	5714.8	7/22/2014

2	Name2	26.976811	5921.68	7/18/2014


期望获取到每个ID，每个Name在每个月的上中下旬的数据统计
ID	Name	6-上旬-Total1	6-上旬-Total2	6-中旬-Total1	6-中旬-Total2	6-下旬-Total1	6-下旬-Total2	7-上旬-Total1	7-上旬-Total2	7-中旬-Total1	7-中旬-Total2	7-下旬-Total1	7-下旬-Total2

1	Name1	0	0	8.981132	476	0	0	0	0	0	0	0	0

1	Name2	0	0	0	0		0	9	18	0	0	0	0

1	Name3											14.589062	1203.16

1	Name4									30.750759	2023.4		

2	Name1			7	7								

2	Name2											13.491984	5714.8

2	Name2									26.976811	5921.68

...全文
344 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
yjjtwo 2015-05-29
  • 打赏
  • 举报
 回复
引用 7 楼 kielin 的回复:
[quote=引用 5 楼 ky_min 的回复:] 
DECLARE @MONTH DATETIME
SET @MONTH='2014-07-01'
SELECT ID,Name
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [preSelectedMonth-f-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [preSelectedMonth-f-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [preSelectedMonth-m-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [preSelectedMonth-m-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [preSelectedMonth-l-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [preSelectedMonth-l-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [SelectedMonth-f-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [SelectedMonth-f-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [SelectedMonth-m-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [SelectedMonth-m-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [SelectedMonth-l-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [SelectedMonth-l-Total2]
FROM TB
感觉只能这样了
谢谢！[/quote] 这个只能用在创建的临时表吗 试了下数据库里建的表，提示对象名 “表名1” 无效
还在加载中灬 2014-11-21
  • 打赏
  • 举报
 回复
问题解决了? 分可以随意分配的，总分等于贴子分即可
kielin 2014-11-21
  • 打赏
  • 举报
 回复
[quote=引用 6 楼 tangguangqiang 的回复:] quote] 怎么给不分给你，只能给一个人？
kielin 2014-11-21
  • 打赏
  • 举报
 回复
引用 6 楼 tangguangqiang 的回复:


use tempdb
create table test(id int,[name] varchar(30),total1 decimal(18,6),total2 decimal(18,6),date1 smalldatetime)
insert into test
select 1,	'Name1',	8.981132,	  476	,'6/15/2014' union all
select 1,	'Name2',	9	     ,    18	,'7/9/2014'  union all
select 1,	'Name3',	14.589062,	1203.16	,'7/25/2014' union all
select 1,	'Name4',	30.750759,	2023.4	,'7/11/2014' union all
select 2,	'Name1',	7	      ,  7	    ,'6/13/2014' union all
select 2,	'Name2',	13.491984,	5714.8	,'7/22/2014' union all
select 2,	'Name2',	26.976811,	5921.68	,'7/18/2014' 

declare @sql varchar(max)
select @sql=isnull(@sql+',','')+' sum(case when day(date1) between 1 and 10 then total1 else 0 end)  as ['+ cast(month(date1) as varchar)+'-上旬-total1],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total1 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total1],'+char(10)+
' sum(case when day(date1)>20 then total1 else 0 end)  as [' +cast(month(date1) as varchar)+'-下旬-total1],'+char(10)+
' sum(case when day(date1) between 1 and 10 then total2 else 0 end)  as ['+ cast(month(date1) as varchar)+'-上旬-total2],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total2],'+char(10)+
' sum(case when day(date1)>20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'下旬-total2]'
from test group by month(date1)

set @sql='select id,[name],'+@sql
+'from test group by id,name,convert(varchar(6),date1,112)'
--print @sql
exec(@sql)
谢谢
kielin 2014-11-21
  • 打赏
  • 举报
 回复
引用 5 楼 ky_min 的回复:
DECLARE @MONTH DATETIME
SET @MONTH='2014-07-01'
SELECT ID,Name
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [preSelectedMonth-f-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [preSelectedMonth-f-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [preSelectedMonth-m-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [preSelectedMonth-m-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [preSelectedMonth-l-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [preSelectedMonth-l-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [SelectedMonth-f-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [SelectedMonth-f-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [SelectedMonth-m-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [SelectedMonth-m-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [SelectedMonth-l-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [SelectedMonth-l-Total2]
FROM TB
感觉只能这样了
谢谢！
kielin 2014-11-21
  • 打赏
  • 举报
 回复
解决了，谢谢你们啊
习惯性蹭分 2014-11-20
  • 打赏
  • 举报
 回复 


use tempdb
create table test(id int,[name] varchar(30),total1 decimal(18,6),total2 decimal(18,6),date1 smalldatetime)
insert into test
select 1,	'Name1',	8.981132,	  476	,'6/15/2014' union all
select 1,	'Name2',	9	     ,    18	,'7/9/2014'  union all
select 1,	'Name3',	14.589062,	1203.16	,'7/25/2014' union all
select 1,	'Name4',	30.750759,	2023.4	,'7/11/2014' union all
select 2,	'Name1',	7	      ,  7	    ,'6/13/2014' union all
select 2,	'Name2',	13.491984,	5714.8	,'7/22/2014' union all
select 2,	'Name2',	26.976811,	5921.68	,'7/18/2014' 

declare @sql varchar(max)
select @sql=isnull(@sql+',','')+' sum(case when day(date1) between 1 and 10 then total1 else 0 end)  as ['+ cast(month(date1) as varchar)+'-上旬-total1],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total1 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total1],'+char(10)+
' sum(case when day(date1)>20 then total1 else 0 end)  as [' +cast(month(date1) as varchar)+'-下旬-total1],'+char(10)+
' sum(case when day(date1) between 1 and 10 then total2 else 0 end)  as ['+ cast(month(date1) as varchar)+'-上旬-total2],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total2],'+char(10)+
' sum(case when day(date1)>20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'下旬-total2]'
from test group by month(date1)

set @sql='select id,[name],'+@sql
+'from test group by id,name,convert(varchar(6),date1,112)'
--print @sql
exec(@sql)
还在加载中灬 2014-11-20
  • 打赏
  • 举报
 回复 
DECLARE @MONTH DATETIME
SET @MONTH='2014-07-01'
SELECT ID,Name
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [preSelectedMonth-f-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [preSelectedMonth-f-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [preSelectedMonth-m-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [preSelectedMonth-m-Total2]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [preSelectedMonth-l-Total1]
	,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [preSelectedMonth-l-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [SelectedMonth-f-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [SelectedMonth-f-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [SelectedMonth-m-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [SelectedMonth-m-Total2]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [SelectedMonth-l-Total1]
	,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [SelectedMonth-l-Total2]
FROM TB
感觉只能这样了
kielin 2014-11-20
  • 打赏
  • 举报
 回复
引用 3 楼 kielin 的回复:
[quote=引用 2 楼 ky_min 的回复:] 这样的话，后面的列名随数据在变化，只能是动态处理了，有异议吗? 你肯定不希望列名写死为6月 7月吧
是的，随数据中月份值变化。可以取为preSelectedMonth-f, preSelectedMonth-m,preSelected-l,及SelectedMonth-f,SlectedMonth-m,SelectedMonth-l [/quote] 我只需要所选月份及前一个月的数据。 sql server 2008。
kielin 2014-11-20
  • 打赏
  • 举报
 回复
引用 2 楼 ky_min 的回复:
这样的话，后面的列名随数据在变化，只能是动态处理了，有异议吗? 你肯定不希望列名写死为6月 7月吧
是的，随数据中月份值变化。可以取为preSelectedMonth-f, preSelectedMonth-m,preSelected-l,及SelectedMonth-f,SlectedMonth-m,SelectedMonth-l
还在加载中灬 2014-11-20
  • 打赏
  • 举报
 回复
这样的话，后面的列名随数据在变化，只能是动态处理了，有异议吗? 你肯定不希望列名写死为6月 7月吧
kielin 2014-11-20
  • 打赏
  • 举报
 回复
原始数据图片

期望结果的图片：

谢谢大家。
咪博士零基础轻松学Python（图解实例）
【你将收获什么？】1. 提升动手能力：老师直播写代码、现场修复 bug。让你彻底告别“一看就会，一写就废”！2. 培养学习兴趣：100% 全部案例驱动，每堂课都让你“学有所成”，让你爱上编程，从此不再畏惧。3. 扎实编程基础：锻炼编程的逻辑思维，养成优秀的编码习惯，为进一步学习打下坚实基础。【需要什么基础？】1. 掌握基本的电脑操作，能使用 Word 编辑文档；2. 具有中学数学基础，理解变量、函数等基本概念。【课程更新时间？】平均每天至少更新 1 个课时；最迟 9 月下旬全部更新完成。 
题解 | #工作日各时段叫车量、等待接单时间和调度时间#
后续三月份应该有很多厂会开暑期实习，但是按照本科的经验应该招的是25届的，我现在。公司业务扩大，招家政规划师，办公室文员，家政经纪人，月薪8000-50000上不封顶，早9点晚6，月休四天，享国家法定假日，午休2...
第五届土木建筑及灾害防控国际学术会议（CADPC 2024）
25届菜鸟，现在有一个本地的小厂日常实习offer，应该是过完年后2月中下旬入职接项目干活，现在还有一个月的空窗期，想找一个更好的暑期实习，但是暑期实习我看牛客。23届，往届，考公，考研都可，你们赶紧来找栗栗啊...
ElasticSearch实战指南必知必会：分词器、打分机制
秋招签了兆芯的三方，数字ic后端，然后又拿到了小米的sp，该不该毁约去小米，违约金2w多1. 兆芯 N*15，15％公积金，9852.小米 (N+4)*15，1。楼主双9硕，非科班，南方人，期望base地目前是杭州/上海，个人不是很想去...
虎牙社招（20231222）岗位有限建议尽早投递
晒一晒我的offer#各位大佬们，请教一下我的简历问题有啥需要修改的嘛，目前是在青岛上学，因为我明年毕业，过年之前就离校了，等明年毕业答辩再回来，然后只能先找一份实习岗位，但是青岛这。最近小弟遇见一个棘手的...
疑难问题

22,210

社区成员

121,729

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章