sum()函数中使用排序语句的怪现象

allanli 2011-07-19 03:06:35

在这边重新开个贴,基础性的东西,呵呵
原帖请看:http://topic.csdn.net/u/20110718/13/7267b9b0-76e2-40b9-9c9f-3d11db85a9d9.html?99610

我们没有装2000以上版本的SQL Server,谁有的可以测试一下下面语句:



select '600001' as companycode,'20110715' as PriceDate,15.5 as PriceEnd

into #DayPrice

union all

select '600001' as companycode,'20110714' as PriceDate,15.4 as PriceEnd

union all

select '600001' as companycode,'20110713' as PriceDate,15.3 as PriceEnd

union all

select '600001' as companycode,'20110712' as PriceDate,15.2 as PriceEnd

union all

select '600001' as companycode,'20110711' as PriceDate,15.1 as PriceEnd

union all 

select '600002' as companycode,'20110715' as PriceDate,25.5 as PriceEnd

union all

select '600002' as companycode,'20110714' as PriceDate,25.4 as PriceEnd

union all

select '600002' as companycode,'20110713' as PriceDate,25.3 as PriceEnd

union all

select '600002' as companycode,'20110712' as PriceDate,25.2 as PriceEnd

union all

select '600002' as companycode,'20110711' as PriceDate,25.1 as PriceEnd



select a.companycode,count(a.PriceDate) as DayCount

,(select sum(PriceEnd) from 

(select top 3 PriceEnd from #DayPrice as aa where aa.companycode=a.companycode order by PriceDate desc) t

) as Price3

from #DayPrice as a

group by a.companycode

go



drop table #DayPrice



/******************************************(2000是这样的结果)

companycode DayCount Price3

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600001    5    46.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2

600002    5    76.2



--------------------------

--我希望的应该是返回(2008返回这样的结果)

companycode DayCount Price3

600001    5    46.2

600002    5    76.2

...全文

223 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

IE11下面经常卡到爆 2011-07-20

打赏
举报

的确很奇怪，关注一下
把where aa.companycode=a.companycode注释掉呢？

allanli 2011-07-20

打赏
举报

[Quote=引用 9 楼 cd731107 的回复:]

确实如楼主所言，2000里面是很难，改了一下就能用了，看下面
SQL code
select a.companycode,(select sum(PriceEnd) from #DayPrice aa where companycode=a.companycode
and PriceDate in (select top 3 PriceDate from #DayPrice bb
whe……
[/Quote]

终于找到一个可以实现我要求的2000的写法了,呵呵
再等等大家的讨论,没有更好结果的结贴给分

cd731107 2011-07-20

打赏
举报

不是在９楼已经写好了吗，测试过的

allanli 2011-07-20

打赏
举报

[Quote=引用 3 楼 acherat 的回复:]

SQL code

select '600001' as companycode,'20110715' as PriceDate,15.5 as PriceEnd
into #DayPrice
union all
select '600001' as companycode,'20110714' as PriceDate,15.4 as PriceEnd
union all
select '6……
[/Quote]
你确认是在sql server 2000中执行返回这样的结果吗?
我是返回第一种结果的

allanli 2011-07-20

打赏
举报

[Quote=引用 14 楼 huang_yungui0515 的回复:]

字段再套一个SUM。 2楼应该是正确的
select a.companycode,count(a.PriceDate) as DayCount
,sum(select top 3 PriceEnd from #DayPrice as aa where aa.companycode=a.companycode order by PriceDate desc) as Price3
from #……
[/Quote]

这样写法在2000没法通过语法检测,不能这样写的

yinhaichao2008 2011-07-19

打赏
举报

sql2005执行结果如下
600001 5 46.2
600002 5 76.2

Well 2011-07-19

打赏
举报

检查执行计划。。。

yubofighting 2011-07-19

打赏
举报

[Quote=引用 8 楼 acherat 的回复:]
SQL code

--2000

select '600001' as companycode,'20110715' as PriceDate,15.5 as PriceEnd
into #DayPrice
union all
select '600001' as companycode,'20110714' as PriceDate,15.4 as PriceEnd
union……
[/Quote]

奇怪的是加distinct还是一样。。。。

右脚小拇指 2011-07-19

打赏
举报

字段再套一个SUM。 2楼应该是正确的
select a.companycode,count(a.PriceDate) as DayCount
,sum(select top 3 PriceEnd from #DayPrice as aa where aa.companycode=a.companycode order by PriceDate desc) as Price3
from #DayPrice as a
group by a.companycode

--小F-- 2011-07-19

打赏
举报

没测试环境进来围观

oO寒枫Oo 2011-07-19

打赏
举报

多谢11

cd731107 2011-07-19

打赏
举报

[Quote=引用 10 楼 lxpbs8851 的回复:]
谁帮忙试试5楼的呵呵。
[/Quote]
服务器: 消息 207，级别 16，状态 3，行 1
列名 'companycode' 无效。

oO寒枫Oo 2011-07-19

打赏
举报

谁帮忙试试5楼的呵呵。

cd731107 2011-07-19

打赏
举报

确实如楼主所言，2000里面是很难，改了一下就能用了，看下面

select a.companycode,(select sum(PriceEnd) from #DayPrice aa where companycode=a.companycode 

and PriceDate in (select top 3 PriceDate from #DayPrice bb 

where  companycode=a.companycode order by PriceDate desc))

from #DayPrice as a

group by a.companycode

AcHerat 元老 2011-07-19

打赏
举报



--2000



select '600001' as companycode,'20110715' as PriceDate,15.5 as PriceEnd

into #DayPrice

union all

select '600001' as companycode,'20110714' as PriceDate,15.4 as PriceEnd

union all

select '600001' as companycode,'20110713' as PriceDate,15.3 as PriceEnd

union all

select '600001' as companycode,'20110712' as PriceDate,15.2 as PriceEnd

union all

select '600001' as companycode,'20110711' as PriceDate,15.1 as PriceEnd

union all 

select '600002' as companycode,'20110715' as PriceDate,25.5 as PriceEnd

union all

select '600002' as companycode,'20110714' as PriceDate,25.4 as PriceEnd

union all

select '600002' as companycode,'20110713' as PriceDate,25.3 as PriceEnd

union all

select '600002' as companycode,'20110712' as PriceDate,25.2 as PriceEnd

union all

select '600002' as companycode,'20110711' as PriceDate,25.1 as PriceEnd

go



select a.companycode,count(a.PriceDate) as DayCount

,(select sum(PriceEnd) from 

(select top 3 PriceEnd from #DayPrice as aa where aa.companycode=a.companycode order by PriceDate desc) t

) as Price3

from #DayPrice as a

group by a.companycode



drop table #DayPrice



/***********



companycode DayCount    Price3                                   

----------- ----------- ---------------------------------------- 

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600001      5           46.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

600002      5           76.2

楼主查询加个distinct吧！

pt1314917 2011-07-19

打赏
举报

结果没问题噢。

yubofighting 2011-07-19

打赏
举报

是否跟排序规则有关，等高人解答

oO寒枫Oo 2011-07-19

打赏
举报



select a.companycode,

(select count(1) from #DayPrice b where a.companycode=b.companycode) as DayCount,

(select sum(PriceEnd) from 

  (select top 3 PriceEnd from #DayPrice b where a.companycode=b.companycode order by PriceDate desc) c

)  Price3

from  (select distinct companycode from #DayPrice) a

P1mm 2011-07-19

打赏
举报

Try:

order by PriceDate desc
-->

order by aa.PriceDate desc

AcHerat 元老 2011-07-19

打赏
举报



select '600001' as companycode,'20110715' as PriceDate,15.5 as PriceEnd

into #DayPrice

union all

select '600001' as companycode,'20110714' as PriceDate,15.4 as PriceEnd

union all

select '600001' as companycode,'20110713' as PriceDate,15.3 as PriceEnd

union all

select '600001' as companycode,'20110712' as PriceDate,15.2 as PriceEnd

union all

select '600001' as companycode,'20110711' as PriceDate,15.1 as PriceEnd

union all 

select '600002' as companycode,'20110715' as PriceDate,25.5 as PriceEnd

union all

select '600002' as companycode,'20110714' as PriceDate,25.4 as PriceEnd

union all

select '600002' as companycode,'20110713' as PriceDate,25.3 as PriceEnd

union all

select '600002' as companycode,'20110712' as PriceDate,25.2 as PriceEnd

union all

select '600002' as companycode,'20110711' as PriceDate,25.1 as PriceEnd

go



select a.companycode,count(a.PriceDate) as DayCount

,(select sum(PriceEnd) from 

(select top 3 PriceEnd from #DayPrice as aa where aa.companycode=a.companycode order by PriceDate desc) t

) as Price3

from #DayPrice as a

group by a.companycode

go



drop table #DayPrice



/*********



companycode DayCount    Price3

----------- ----------- ---------------------------------------

600001      5           46.2

600002      5           76.2



(2 行受影响)