导航
  • 主页
会员中心
收藏
消息
创作中心

关于group by不能使用外部参数的问题

yeshucheng 个体 技术总监/研发总监  2009-10-27 06:44:02
例，有一条sql语句：
select
cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR,
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL,
from CTR C
where C.CTRBOOKID=104
and C.SLRID =1081
and (C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 )
group by
cast(case when (C.SLRID =1081 ) then 'Sales' else 'Purchases' end as VARCHAR(10)),
C.SHIPMONTHNUMBER,
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50))

首先group by 不能使用别名，所以在group by中使用全称，现在的问题是：这当中的'201001'找个数据是从外围页面传进来的参数，所以就无法分组了，请问如何解决找个问题呢？
...全文
109 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
SQL77 2009-10-27
[Quote=引用 13 楼 yeshucheng 的回复:]
select
cast(1 as INTEGER) as COLS,
cast(case when (C.SLRID =1079 ) then 'Sales' else 'Purchases' end as VARCHAR(10)) as CONTRACTTYPE,
C.SHIPMONTHNUMBER, 
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR,
cast(C.SHIPMONTHNUMBER+'00' as VARCHAR(10)) as KEYCOL,
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL,
cast(0.00 as NUMERIC(18,4)) as PIVOTFIELD,
cast(JCS.SHORTNAME +' <br/>Qty <br/>&#40MT)' as VARCHAR(50)) as LABEL
from CTR C
join CTRACTIVE CA on (C.CTRID = CA.CTRID)
join JCIVITEMS JI on (C.COMMODITYID = JI.IVITEMID)
join JCSHIPMONTHSMAPPING JCS on (C.SHIPMONTHNUMBER = JCS.MONTHNUMBER)
where C.CTRBOOKID=102 and C.SLRID =1079
and (C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 ) 
group by
cast(case when (C.SLRID =1079 ) then 'Sales' else 'Purchases' end as VARCHAR(10)),
C.SHIPMONTHNUMBER, 
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)),
cast(JCS.SHORTNAME +' <br/>Qty <br/>&#40MT)' as VARCHAR(50))

这个就是原始的SQL,关键它还需要和其它几个做union,问题的就是出在：
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)),
[/Quote]
参考先转成子查询,就可以按照别名字段来分组
 回复 赞
yeshucheng 2009-10-27
select
cast(1 as INTEGER) as COLS,
cast(case when (C.SLRID =1079 ) then 'Sales' else 'Purchases' end as VARCHAR(10)) as CONTRACTTYPE,
C.SHIPMONTHNUMBER,
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR,
cast(C.SHIPMONTHNUMBER+'00' as VARCHAR(10)) as KEYCOL,
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL,
cast(0.00 as NUMERIC(18,4)) as PIVOTFIELD,
cast(JCS.SHORTNAME +'<br/>Qty<br/>&#40MT)' as VARCHAR(50)) as LABEL
from CTR C
join CTRACTIVE CA on (C.CTRID = CA.CTRID)
join JCIVITEMS JI on (C.COMMODITYID = JI.IVITEMID)
join JCSHIPMONTHSMAPPING JCS on (C.SHIPMONTHNUMBER = JCS.MONTHNUMBER)
where C.CTRBOOKID=102 and C.SLRID =1079
and (C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 )
group by
cast(case when (C.SLRID =1079 ) then 'Sales' else 'Purchases' end as VARCHAR(10)),
C.SHIPMONTHNUMBER,
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)),
cast(JCS.SHORTNAME +'<br/>Qty<br/>&#40MT)' as VARCHAR(50))

这个就是原始的SQL,关键它还需要和其它几个做union,问题的就是出在：
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)),
回复 赞
navy887 2009-10-27
[Quote=引用 9 楼 bancxc 的回复:]
用 exec()
[/Quote]
大角牛也换头像了啊。。
 回复 赞
devilidea 2009-10-27
哦
 回复 赞
SQL77 2009-10-27
select * form 

(

select 

	cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR, 

	cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL,

	* 

from 	

	CTR C 

where 

	C.CTRBOOKID=104 

and

	 C.SLRID =1081 

and 	

	(C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 )  

)as t



group by 

--cast(case when (C.SLRID =1081 ) then 'Sales' else 'Purchases' end as VARCHAR(10)), 

SHIPMONTHNUMBER,  

COMMODITYID,

CTRMONTHYEAR,

ORDERCOL
 回复 赞
bancxc 2009-10-27
用 exec()
 回复 赞
SQL77 2009-10-27
[Quote=引用 5 楼 yeshucheng 的回复:]

我是说不使用存储过程呢？如何处理

[/Quote]

select * form 

(

select 

	cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR, 

	cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL, 

from 	

	CTR C 

where 

	C.CTRBOOKID=104 

and

	 C.SLRID =1081 

and 	

	(C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 )  

)as t


你那写法不对吧,分组后面是字段呀,

你可以用子查询再用字段分组,呵呵,刚没看清楚
 回复 赞
navy887 2009-10-27
不确定的用参数替代，然后用动态SQL
 回复 赞
SQL77 2009-10-27
[Quote=引用 5 楼 yeshucheng 的回复:]
我是说不使用存储过程呢？如何处理
[/Quote]
不行啊,你要动态的分组,好像只能用动态的执行才行的
 回复 赞
yeshucheng 2009-10-27
我是说不使用存储过程呢？如何处理
 回复 赞
SQL77 2009-10-27
[Quote=引用 3 楼 yeshucheng 的回复:]
现在的问题是，找个SQL语句是从firebird修改过来的，所以也就不好过多修改？能否不使用存储过程呢？
非常谢谢你的回答
[/Quote]
当然可以用存储过程呀

动态执行
 回复 赞
yeshucheng 2009-10-27
现在的问题是，找个SQL语句是从firebird修改过来的，所以也就不好过多修改？能否不使用存储过程呢？
非常谢谢你的回答
 回复 赞
nianran520 2009-10-27
你这样写没报错？聚合函数呢
 回复 赞
7761098 2009-10-27
動態sql了，用EXEC('select
cast(substring('201001',1,4)+'12' as VARCHAR(6)) as CTRMONTHYEAR,
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) as ORDERCOL,
from CTR C
where C.CTRBOOKID=104
and C.SLRID =1081
and (C.SHIPMONTHNUMBER >=200910 and C.SHIPMONTHNUMBER <=201001 )
group by
cast(case when (C.SLRID =1081 ) then 'Sales' else 'Purchases' end as VARCHAR(10)),
C.SHIPMONTHNUMBER,
C.COMMODITYID,
cast(substring('201001',1,4)+'12' as VARCHAR(6)),
cast(upper(C.COMMODITYID)+substring('201001',1,4)+'1201' as VARCHAR(50)) ')
的方式吧，可以外部傳遞數據入來，你拼湊一下那些單引號
 回复 赞
发动态
发帖子
应用实例
创建于2007-09-28

2.7w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
申请成为版主
社区公告
暂无公告