消去重复项？为什么不行？？

[bezier1980]

SELECT distinct sght.khdm,SUM(sghtsp.spsl) AS spsl, SUM(sghtsp.spdj) AS spje, sum(sghtsp.spdj/sghtsp.spsl) AS pjdj, spdj/SUM(sghtsp.spdj) AS xsb
FROM sght,sghtsp
where sght.hth=sghtsp.hth
group BY khdm,spdj
order by khdm

问题是想消去khdm相同的记录，加上spdj/SUM(sghtsp.spdj) AS xsb这条语句就不行，
非要在group by 中加入spdj。 怎么样解决这个问题，感谢！！

[leimin]

SELECT sght.khdm,SUM(sghtsp.spsl) AS spsl, SUM(sghtsp.spdj) AS spje, sum(sghtsp.spdj/sghtsp.spsl) AS pjdj, max(spdj)/SUM(sghtsp.spdj) AS xsb
FROM sght,sghtsp
where sght.hth=sghtsp.hth
group BY khdm
order by khdm

[yczzg]

select t.khdm,avg(t.spsl) as spsl,avg(t.spje) as spje,avg(t.pjdj) as pjdj,avg(t.xsb) as xsb
from
(SELECT distinct sght.khdm,SUM(sghtsp.spsl) AS spsl, SUM(sghtsp.spdj) AS spje, sum(sghtsp.spdj/sghtsp.spsl) AS pjdj, spdj/SUM(sghtsp.spdj) AS xsb
FROM sght,sghtsp
where sght.hth=sghtsp.hth
group BY khdm,spdj
order by khdm)t
group by t.khdm

[bezier1980]

在查询分析器提示此错误！！“ An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.”
什么意思？？

[lfengxu]

declare @a varchar(8000),@b int,@c int
SELECT distinct sght.khdm,SUM(sghtsp.spsl) AS spsl, SUM(sghtsp.spdj) AS spje, sum(sghtsp.spdj/sghtsp.spsl) AS pjdj, spdj/SUM(sghtsp.spdj) AS xsb gr,cast('' as varchar(8000)) value into #aaa
FROM sght,sghtsp
where sght.hth=sghtsp.hth
set @c=0
update #aaa set @a=case when @b=重复的编号 then @a else '' end+重复的字段名+',',
@c=case when @b=重复的编号 then @c else @c+1 end,@b=重复的编号,gr=@c,value=@a
select max(重复的编号) as id,max(value) result into #bbb from #aaa group by gr
然后就是通过#aaa,#bbb查找你要的信息就可以拉。。