因为该列没有包含在聚合函数或 GROUP BY 子句中,强加group by数据不对

[w87875251l]

select
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介]

from agt_trad..WFPUSER_A0301 a
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname and a.objid <>0 and a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid where
cast(isnull(t.AM0010,0) as decimal(12,2)) between 1 and 10 group by a.AD0506,a.AC0011
order by t.AM0010

这样写报错， ORDER BY 子句中的列 "agt_trad..WFPUSER_A0304.AM0010" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
如果group by a.AD0506,a.AC0011.t.AM0010 这样强加的话数据不准，
怎么解决

[xuam]

select  
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介]

from agt_trad..WFPUSER_A0301 a  
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname and a.objid <>0 and a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid where  
  cast(isnull(t.AM0010,0) as decimal(12,2)) between 1 and 10 
group by a.AD0506,a.AC0011

[--小F--]

select  
max(isnull(t.AC0534,'')) as [图片显示],
max(a.AD0506) as [产品品牌], ---要么这里聚合
max(a.AC0011) as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介]

from agt_trad..WFPUSER_A0301 a  
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname and a.objid <>0 and a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid where  
  cast(isnull(t.AM0010,0) as decimal(12,2)) between 1 and 10 group by a.AD0506,a.AC0011
order by t.AM0010

[w87875251l]

1楼的，我就是要按价格排序啊，你给去掉了。。。。晕

[w87875251l]

select  
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介]

from agt_trad..WFPUSER_A0301 a  
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname and a.objid <>0 and a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid where  
  cast(isnull(t.AM0010,0) as decimal(12,2)) between 1 and 10 group by a.AD0506,a.AC0011
order by t.AM0010

2楼，这样写报错的

[xuam]

select  
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介]

from agt_trad..WFPUSER_A0301 a  
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname and a.objid <>0 and a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid 
where cast(isnull(t.AM0010,0) as decimal(12,2)) between 1 and 10 
group by a.AD0506,a.AC0011
order by cast(isnull(t.AM0010,0) as decimal(12,2))

[--小F--]

agt_trad..WFPUSER_A0304.AM0010这个在楼主的语句中没有发现不聚合的地方


如果有就 MAX一下就OK了

[PB菜鸟]

[Quote=引用楼主 w87875251l 的回复:]
select
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal……
[/Quote]

可以先gruop by取出结果，然后再order by t.AM0010

[w87875251l]

自己解决,order by 别名

select 
max(isnull(t.AC0534,'')) as [图片显示],
a.AD0506 as [产品品牌],
a.AC0011 as [产品编号],
max(isnull(a.AC0012,'')) as [厂家货号],
max(isnull(a.EC0104,'')) as [产品名称],
max(cast(isnull(a.AM0006,0) as decimal(12,2))) as [市场价],
max(cast(isnull(a.AM0007,0) as decimal(12,2))) as [零售价],
max(cast(isnull(t.AM0010,0) as decimal(12,2))) as [销售价],
max(isnull(a.AC0013,'')) as [产品简介],
max(convert(char(8),a.Objdatetime,112)) as [上架日期],
max(cast(isnull(b.AM0008,0) as decimal(12,2))) as [包装费],
max(isnull(b.AM0009,0)) as [毛重比例]
,max((case when b.AD0506 is null then b2.AD0506 else b.AD0506 end) ) [所属分类]
,max(isnull(a.AD2503,'')) [供应商]
,max(isnull(a.objpy,'')) as [拼音]
,max(a.Objid) Objid,max(a.Parentid) parentid ,max(isnull(c.Objjc,'')) objjc 


from agt_trad..WFPUSER_A0301 a 
join agt_trad..WFPUSER_A0304 t on a.AC0011=t.AC0011 and a.AD0506=t.AD0506
join agt_trad..WFPCODE_AD502 c on a.AD0506=c.objname 

and a.objid <>0 and  a.AB0001=0 and isnull(a.AD0520,'0')='0'
join agt_trad..WFPUSER_A0301 b on a.parentid=b.objid
left join agt_trad..WFPUSER_A0301 b2 on b.parentid=b2.objid where 
  (c.Objjc like '%%' or b.AD0506 like '%%' or  a.EC0104 like '%%' or
(case when b.AD0506 is null then b2.AD0506 else b.AD0506 end) like '%%'
)   group by a.AD0506,a.AC0011

order by 销售价

[nzperfect]

只有t.AM0010与group by group by a.AD0506,a.AC0011的行存在存一对应，你才可以order by t.AM0010
不然肯定不行，显然还没理解什么是group by