有一个列的字段如下:
.44
.45
.78
.34
.71
.28
.1
.3
.61
.71
这个字段的值是一个比率,现在想要对它们来求平均,结果出现了nvarchar 对于 avg 运算符无效。的错,把它转成FLOAT或是NUUMERIC不能都没有成功,转不了,要怎么办呢?
.44
.45
.78
.34
.71
.28
.1
.3
.61
.71
这个字段的值是一个比率,现在想要对它们来求平均,结果出现了nvarchar 对于 avg 运算符无效。的错,把它转成FLOAT或是NUUMERIC不能都没有成功,转不了,要怎么办呢?
解决方案 »
- 对于sql的担心
- 删除数据库中所有表里日期是3个月之前的记录
- sqlserver 2000如何计算累计百分比(上一行与下一行相加)
- dbvisualizer配置MySql连接问题
- mmc检测到此管理单元发生一个错误
- 数据字典与数据表有何区别,如何定义数据表的主键!
- 代码创建视图或表
- 请大家帮下忙,我的SQLSERVER2005的配置管理器打不开,老是显示“无法连接到WMI提供程序。您没有权限或者该服务器无法访问...”
- 在修改windows登陆密码后应该如何修改sql server登陆密码?在线等
- 请SQL SERVER高手帮忙??
- 高手指教
- 怎样通过SQL命令查询SQL Server中Link Server的信息
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-28 15:43:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(3))
insert [tb]
select '.44' union all
select '.45' union all
select '.78' union all
select '.34' union all
select '.71' union all
select '.28' union all
select '.1' union all
select '.3' union all
select '.61' union all
select '.71'
--------------开始查询--------------------------select avg(cast(substring(col,2,len(col)-1) as int)) from [tb]
----------------结果----------------------------
/* -----------
43(1 行受影响)
*/
go
create table [tb]([col] varchar(3))
insert [tb]
select '.44' union all
select '.45' union all
select '.78' union all
select '.34' union all
select '.71' union all
select '.28' union all
select '.1' union all
select '.3' union all
select '.61' union all
select '.71'
select avg(convert(NUMERIC(6,2),'0'+col))
from tb;
go
create table [tb]([col] varchar(3))
insert [tb]
select '.44' union all
select '.45' union all
select '.78' union all
select '.34' union all
select '.71' union all
select '.28' union all
select '.1' union all
select '.3' union all
select '.61' union all
select '.71'
select avg(convert(NUMERIC(6,2),col))
from tb;
go
create table [tb]([col] varchar(3))
insert [tb]
select '.44' union all
select '.45' union all
select '.78' union all
select '.34' union all
select '.71' union all
select '.28' union all
select '.1' union all
select '.3' union all
select '.61' union all
select '.71'
select avg(convert(NUMERIC(6,2),col))
from tb;
---------------------------------------------
0.472000
then cast(substring('0'+col_name,1,len(aa)+1) as float)
else cast(col_name as float) end)
from table_name
then cast(substring('0'+col_name,1,len(col_name)+1) as float)
else cast(col_name as float) end)
from table_name
from tb;
---------------------------------------------
0.472000select cast(SUBSTRING ( col ,2, len(col)) as int)
from tb;
-------------------------------------------------------
44 --.44变成44
45
78
34
71
28
1
3 --.3 变成3
61
71-- 44是3的多少倍?而0.44是0.3的多少倍?
--呵呵....................................--这样的低级错误,我说了大半天,竟然没有人能够理解!
--我只能说,两个字:悲哀!
2> go
col
---
.44
.45
.78
.34
.71
.28
.1
.3
.61
.71(10 rows affected)
1> select sum(cast (col as decimal(10,4))) from tb
2> go----------------------------------------
4.7200(1 rows affected)
1>
SELECT @v='.54'
SELECT CAST(@v AS NUMERIC(16,2))
--------------------------------------
0.54