我有一张表
create table test(
id1 int,
id2 int,
name varchar(32),
address varchar(32),
postcode varchar(32)
)下面的两个sql 执行效果不同,不知道为什么
select when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by id, name, postcodeselect when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by id, name, when name is NULL then 'a' else postcode end谢谢
create table test(
id1 int,
id2 int,
name varchar(32),
address varchar(32),
postcode varchar(32)
)下面的两个sql 执行效果不同,不知道为什么
select when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by id, name, postcodeselect when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by id, name, when name is NULL then 'a' else postcode end谢谢
select case when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by name, postcodeselect case when name is NULL then 'a' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by name, case when name is NULL then 'a' else postcode end
但非真正的postcode字段,不会被order by postcode聚合
你可试试
select 'a' postcode from test group by poscode
select 'a' postcode from test group by postcode
它返回的结果'a'是不会被不会被order by postcode聚合另外,的确正常情况下,group by 不支持别名,但就是为了得到像楼主这样的要求,在case情况下,它支持别名
是的,第二个sql是我想要的结果。现在还是要回到我们另外一个问题上,我对第二个sql做参数化
select case when name = 'yourname' then 'const' else postcode end postcode,
sum(id1 - id2) id, name
from test
group by name, case when name = 'yourname' then 'const' else postcode end
成
select case when name = ? then ? else postcode end postcode,
sum(id1 - id2) id, name
from test
group by name, case when name = ? then ? else postcode end
不能被ado执行, 是为什么呢?你可以用
catch(_com_error &e)来捕捉异常信息
说的有些高深,我想问的是,能不能用ado的parameter去参数化group by 子句?有没有相关的文档可以参考?
http://community.csdn.net/Expert/topic/3973/3973759.xml?temp=.7709925