SELECT [ClassID], Sum(cast(Grade as float)) as grade
FROM [Report]
where StuNO in( '200904',200903)
GROUP BY [ClassID]
表的结构是这样的Report(ClassID,StuNO,Grade)
Report表中的这三个字段都是从程序里面加进去的 并且 都是Nvchar类型的
只要我一运行上面的Sql语句他就报错“消息 8114,级别 16,状态 5,第 1 行
从数据类型 nvarchar 转换为 float 时出错。”这是什么原因啊 高手指点一下 是不是Sum(cast(Grade as float)) 不是纯聚合函数啊????
FROM [Report]
where StuNO in( '200904',200903)
GROUP BY [ClassID]
表的结构是这样的Report(ClassID,StuNO,Grade)
Report表中的这三个字段都是从程序里面加进去的 并且 都是Nvchar类型的
只要我一运行上面的Sql语句他就报错“消息 8114,级别 16,状态 5,第 1 行
从数据类型 nvarchar 转换为 float 时出错。”这是什么原因啊 高手指点一下 是不是Sum(cast(Grade as float)) 不是纯聚合函数啊????
if object_id('report') is not null drop table report
gocreate table report
(
ClassID varchar(20),
StuNO varchar(20),
Grade varchar(20)
)insert into report select '1001', '200904', '001'
union all select '3002', '200905', '003'
union all select '2002', '200906', '002'
union all select '3001', '200907', '003'
union all select '3005', '200908', '003' SELECT [ClassID], Sum(cast(Grade as float)) as grade
FROM [Report]
where StuNO in( '200904','200905','200907')
GROUP BY [ClassID]结果
classId grade
1001 1.0
3001 3.0
3002 3.0不知道楼主grade表中的数据能不能转化成float型呢?根据我的表中数据显示没有问题的
if object_id('report') is not null drop table report
gocreate table report
(
ClassID varchar(20),
StuNO varchar(20),
Grade varchar(20)
)insert into report select '1001', '200904', '001'
union all select '3002', '200905', '003'
union all select '2002', '200906', '002'
union all select '3002', '200907', '003'
union all select '3001', '200908', '003' SELECT [ClassID], Sum(cast(Grade as float)) as grade
FROM [Report]
where StuNO in( '200904','200905','200907')
GROUP BY [ClassID]
结果
classId grade
1001 1.0
3002 6.0按classid分组将会使得grade求和
转换失败了,有一些非数字
go
create table Report(ClassID nvarchar(20),StuNO nvarchar(20),Grade nvarchar(20))
insert into Report values ('001','200903','23')
insert into Report values ('001','200904','24')
insert into Report values ('002','200904','25')--查询
SELECT [ClassID], Sum(cast(Grade as float)) as grade
FROM [Report]
where StuNO in( '200904','200903')
GROUP BY [ClassID] --result:
ClassID grade
-------------------- ----------------------
001 47
002 25(2 行受影响)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)
------------------------------------------------------------------------use tempdb
go
create table Report(ClassID nvarchar(20),StuNO nvarchar(20),Grade nvarchar(20))
insert into Report values ('001','200903','23')
insert into Report values ('001','200904','24')
insert into Report values ('002','200904','25')
insert into Report values ('001','200904','a24b')
insert into Report values ('002','200904','cd25')
--查询
/*
该方式适用于数字连续且被字母包含一次的情况,如示例数据,否则可以用SP循环提取数字。
*/
with tbl as (
SELECT [ClassID]
,StuNO
,case when isnumeric(Grade) =1
then cast(Grade as float)
else cast((substring(grade,patindex('%[0-9]%',grade)
,len(Grade)-patindex('%[0-9]%',reverse(grade)))) as float) end Grade
FROM [Report]
)
SELECT [ClassID], Sum(Grade)
FROM tbl
where StuNO in( '200904','200903')
GROUP BY [ClassID] --result:
ClassID
-------------------- ----------------------
001 71
002 50(2 行受影响)
http://www.it118.org/Specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/a1522f2e-402a-4e31-a0d6-684d8760e7d9.htm