语句如下:
select a.OperCode,a.OperName,sum(a.SPZS) as spzs,sum(a.yxzs) as yxzs,sum(b.SJZS) as sjzs,sum(b.SJZS) / sum(a.yxzs) as dlc
from z_KLHZ a left join z_dayfare b on a.OperCode=b.OperCode and a.departdate=b.DepartDate
where a.departdate>='2008-3-1' and a.DepartDate<='2008-4-14'
group by a.OperCode,a.OperName
order by sum(b.SJZS) / sum(a.yxzs) desc
结果:
`OperCode`, `OperName`, `spzs`, `yxzs`, `sjzs`, `dlc`
'2014','¶¿¡ ','10128','7338','1643','0.2239'
'2507','ÍõСÑà ','18928','12966','3299','0.2544'
'2513','ÕÅÅåºì ','18668','13230','3721','0.2813'
'2003','ÂéÀö¾ê ','19933','14721','3735','0.2537'
'2515','ÑîÀöÇÛ ','17647','13332','2232','0.1674'
其它列都能正常排序,最后一列,为什么不按照指定的sum(b.SJZS) / sum(a.yxzs) desc排序?
select a.OperCode,a.OperName,sum(a.SPZS) as spzs,sum(a.yxzs) as yxzs,sum(b.SJZS) as sjzs,sum(b.SJZS) / sum(a.yxzs) as dlc
from z_KLHZ a left join z_dayfare b on a.OperCode=b.OperCode and a.departdate=b.DepartDate
where a.departdate>='2008-3-1' and a.DepartDate<='2008-4-14'
group by a.OperCode,a.OperName
order by sum(b.SJZS) / sum(a.yxzs) desc
结果:
`OperCode`, `OperName`, `spzs`, `yxzs`, `sjzs`, `dlc`
'2014','¶¿¡ ','10128','7338','1643','0.2239'
'2507','ÍõСÑà ','18928','12966','3299','0.2544'
'2513','ÕÅÅåºì ','18668','13230','3721','0.2813'
'2003','ÂéÀö¾ê ','19933','14721','3735','0.2537'
'2515','ÑîÀöÇÛ ','17647','13332','2232','0.1674'
其它列都能正常排序,最后一列,为什么不按照指定的sum(b.SJZS) / sum(a.yxzs) desc排序?
解决方案 »
- 多线程共用ado 连接,交替执行select 和insert时,insert语句总是报这个错误。idispatch error # 3105 Commands
- 请教怎样把数据库中的表和数据批量生成sql语句?
- Powerdesigner添加数据
- at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)错误
- Mysql导出特别慢
- mysql 编译选项问题
- jsp連結資料庫更新問題
- 想建立一个带有自增值的表,为什么不行,脑袋炸了!!!
- 大侠们帮忙看看这是啥玩样
- 自定义函数修改无效
- 一条奇怪的mysql语句
- MySQL5用load data导入数据乱码问题和数据被篡改问题请教
我加上Convert(sum(b.SJZS),decimal(10,4))/convert(sum(a.yxzs),decimal(10,4))
也不解决问题。
[/align]
order by sum(b.SJZS) / IFNULL(sum(a.yxzs),0.1) desc [align=center]==== 思想重于技巧 ====
[/align]
sum(a.yxzs) 无空或0
sum(b.sjzs) 有0
truncate(..,0);
...
order by truncate(ifnull(sum(b.SJZS),0)/sum(a.YXZS),4)
仍不能成功。
有试过吗?[align=center]==== 思想重于技巧 ====
[/align]
遇到除法之类的也就是加一个Convert(money,sum(b.sjzs))/Convert(money,sum(a.yxzs))
没出现过排序出错。郁闷!
问题解决,代码如下:select a.opercode,a.opername,a.yxzs,a.sjzs,a.sjzs/a.yxzs as dcl
from (select a.OperCode,a.OperName,sum(a.yxzs) as yxzs,sum(b.sjzs) as sjzs
From z_klhz a left join z_dayfare b on a.departdate=b.departdate and a.OperCode=b.Opercode
where a.Departdate>='2008-1-1' and a.Departdate<='2008-1-31'
group by a.OperCode,a.OperName) a
order by a.sjzs/a.yxzs desc