菜鸟请教大神一个简单SQL语句问题下班结帖!

happy664618843 2013-05-02 04:23:14





 select   

	Period,

	[Year],  

	'' as  AccountGroup, 

	Project, 

	'' as NaturalCode, 

	ACCNT_NAME,  

	'' as CostCenter,

	sum(Amount) as Amount 

from 

	vw_Journal_Inquiry  

WHERE  

	(PERIOD BETWEEN '201303' AND '201306') 

	And  (Costcenter IN ('0205','020501','020502')) 

group by 

	[Year],

	Period,

	Project, 

	ACCNT_NAME 

ORDER BY 

	[Year],

	Period,

	Project, 

	ACCNT_NAME

我想根据Project进行分组,一个时间段不能出现重复的project,金额最后也是根据project合计我想实现最终结果应该是: 这个SQL该咱写??? 请助! Thanks!
Period Year Project
201303 2013 100012001
201303 2013 员工月度费用
201304 2013 合规有奖问答活动
201304 2013 员工月度费用
上面SQL查询出来的截图如下:现在有重复的Project

...全文

175 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

Blessed_Chuan 2013-05-02

打赏
举报

Project,ACCNT_NAME 参与了分组出现重复

MrYangkang 2013-05-02

打赏
举报

这问题真不是问题

daiyueqiang2045 2013-05-02

打赏
举报

引用楼主 happy664618843 的回复:

 select   
	Period,
	[Year],  
	'' as  AccountGroup, 
	Project, 
	'' as NaturalCode, 
	ACCNT_NAME,  
	'' as CostCenter,
	sum(Amount) as Amount 
from 
	vw_Journal_Inquiry  
WHERE  
	(PERIOD BETWEEN '201303' AND '201306') 
	And  (Costcenter IN ('0205','020501','020502')) 
group by 
	[Year],
	Period,
	Project, 
	ACCNT_NAME 
ORDER BY 
	[Year],
	Period,
	Project, 
	ACCNT_NAME
我想根据Project进行分组,一个时间段不能出现重复的project,金额最后也是根据project合计我想实现最终结果应该是: 这个SQL该咱写??? 请助! Thanks! Period Year Project 201303 2013 100012001 201303 2013 员工月度费用 201304 2013 合规有奖问答活动 201304 2013 员工月度费用上面SQL查询出来的截图如下:现在有重复的Project

lz这个是肯定不同的，你的Project, ACCNT_NAME 参与了分组，所以出现了重复。而且是可以理解的。

开着拖拉机泡妞 2013-05-02

打赏
举报

引用楼主 happy664618843 的回复:

 select   
	Period,
	[Year],  
	'' as  AccountGroup, 
	Project, 
	'' as NaturalCode, 
	ACCNT_NAME,  
	'' as CostCenter,
	sum(Amount) as Amount 
from 
	vw_Journal_Inquiry  
WHERE  
	(PERIOD BETWEEN '201303' AND '201306') 
	And  (Costcenter IN ('0205','020501','020502')) 
group by 
	[Year],
	Period,
	Project, 
	ACCNT_NAME 
ORDER BY 
	[Year],
	Period,
	Project, 
	ACCNT_NAME
我想根据Project进行分组,一个时间段不能出现重复的project,金额最后也是根据project合计我想实现最终结果应该是: 这个SQL该咱写??? 请助! Thanks! Period Year Project 201303 2013 100012001 201303 2013 员工月度费用 201304 2013 合规有奖问答活动 201304 2013 员工月度费用上面SQL查询出来的截图如下:现在有重复的Project

要想你那样可以把ACCNT_NAME这个字段去掉或者这个字段的所有值都一样

开着拖拉机泡妞 2013-05-02

打赏
举报




 select   
	Period,
	[Year],  
	'' as  AccountGroup, 
	Project, 
	'' as NaturalCode, 
	ACCNT_NAME,  --这里是问题
	'' as CostCenter,
	sum(Amount)over(partition by Project) as Amount 
from 
	vw_Journal_Inquiry  
WHERE  
	(PERIOD BETWEEN '201303' AND '201306') 
	And  (Costcenter IN ('0205','020501','020502')) 
ORDER BY 
	[Year],
	Period,
	Project, 
	ACCNT_NAME
你每个ACCNT_NAME都不一样   你那样group by 肯定不行的

-Tracy-McGrady- 2013-05-02

打赏
举报

引用 5 楼 happy664618843 的回复:

[quote=引用 2 楼 yangsh0722 的回复:] select distinct Period,[Year],Project from 表 where 条件 and Project 不为空

不是兄弟说的这么回事现在是要分组不是去重复列[/quote] 你要改写这个语句 select Period,[Year], '' as AccountGroup, Project, '' as NaturalCode, ACCNT_NAME, '' as CostCenter,sum(Amount) as Amount from vw_Journal_Inquiry WHERE (PERIOD BETWEEN '201303' AND '201306') And (Costcenter IN ('0205','020501','020502')) group by [Year],Period,Project, ACCNT_NAME ORDER BY [Year],Period,Project, ACCNT_NAME 按照 Project 分组？

happy664618843 2013-05-02