关于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+

社区成员

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