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

[yeshucheng]

例，有一条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'找个数据是从外围页面传进来的参数，所以就无法分组了，请问如何解决找个问题呢？

[7761098]

動態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)) ')
的方式吧，可以外部傳遞數據入來，你拼湊一下那些單引號

[nianran520]

你这样写没报错？聚合函数呢

[yeshucheng]

现在的问题是，找个SQL语句是从firebird修改过来的，所以也就不好过多修改？能否不使用存储过程呢？
非常谢谢你的回答

[SQL77]

[Quote=引用 3 楼 yeshucheng 的回复:]
现在的问题是，找个SQL语句是从firebird修改过来的，所以也就不好过多修改？能否不使用存储过程呢？
非常谢谢你的回答
[/Quote]
当然可以用存储过程呀

动态执行

[yeshucheng]

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

[SQL77]

[Quote=引用 5 楼 yeshucheng 的回复:]
我是说不使用存储过程呢？如何处理
[/Quote]
不行啊,你要动态的分组,好像只能用动态的执行才行的

[navy887]

不确定的用参数替代，然后用动态SQL

[SQL77]

[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

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

你可以用子查询再用字段分组,呵呵,刚没看清楚

[bancxc]

用 exec()

[SQL77]

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

[devilidea]

哦

[navy887]

[Quote=引用 9 楼 bancxc 的回复:]
用 exec()
[/Quote]
大角牛也换头像了啊。。

[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)),

[SQL77]

[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]
参考先转成子查询,就可以按照别名字段来分组