select [迟到率]= left(cast(cast([迟到人数] as numeric(10,1)) /[人数]*100 as varchar), 5)+'%' from #1
将 varchar 转换为数据类型 numeric 时出现算术溢出错误。
將varchar 改為 varchar(11)或者: select 系部,ltrim(Convert(numeric(9,2),迟到人次*100.0/在校生人数))+'%' As 迟到率 From tb
那个varchar改为11后,依然出现“将 varchar 转换为数据类型 numeric 时出现算术溢出错误。”的错误,不过你的后一种方法有效,我还想请教您一下,如果我那个迟到人次和在校生人数都是通过SQL语句写出来的怎么办?就无法直接用那个列不是吗? SUM(case when late=1 then 1 else 0 end ) as 迟到人次 COUNT(distinct studentNo) as 在校生人数, 求教
可以把你這個查詢的結果放進臨時表里:select 系部,ltrim(Convert(numeric(9,2),迟到人次*100.0/在校生人数))+'%' As 迟到率 From ( ... -- 查詢結果 ) as t
select ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率 From ( select COUNT(distinct studentNo) as 在校生人数, SUM(case when late=1 then 1 else 0 end ) as 迟到人数 from stuAttendence ) as t上边的例子用你的方法算出是迟到率是16.25%,但是用临时表的方法算出是10.94%,还有我想请教一下,最后那个as t,t为什么不显示?它是什么?
是不是因为的80和30都是varchar(50)的原因?
子查詢生成的臨時表需要幫他起個別名:Select * From (子查詢) As t -- t是別名 或者 Select * from tb Inner join (Select * from tb) As t -- t是別名
噢,我记起来了。嘿嘿 SELECT department as 系部, COUNT(distinct studentNo) as 在校生人数, SUM(case when late=1 then 1 else 0 end ) as 迟到人次, ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率 From (select COUNT(distinct studentNo) as 在校生人数,SUM(case when late=1 then 1 else 0 end ) as 迟到人数 from stuAttendence)--临时表 as t --别名from stuAttendence group by department提示“from附近有语法错误”,是不是两个from的事啊? 再帮我一次好吗?我正在学校做一个比较急的项目,寒假的时候我一定要重新学一下SQL
SELECT 系部, 在校生人数, 迟到人次, ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率 From (select department as 系部,COUNT(distinct studentNo) as 在校生人数,SUM(case when late=1 then 1 else 0 end ) as 迟到人数 from stuAttendence group by department)--临时表 as t --别名
select [迟到率]= left(cast(cast([迟到人数] as numeric(10,1)) /[人数]*100 as varchar), 5)+'%' from #1
将 varchar 转换为数据类型 numeric 时出现算术溢出错误。
將varchar 改為 varchar(11)或者:
select 系部,ltrim(Convert(numeric(9,2),迟到人次*100.0/在校生人数))+'%' As 迟到率 From tb
那个varchar改为11后,依然出现“将 varchar 转换为数据类型 numeric 时出现算术溢出错误。”的错误,不过你的后一种方法有效,我还想请教您一下,如果我那个迟到人次和在校生人数都是通过SQL语句写出来的怎么办?就无法直接用那个列不是吗?
SUM(case when late=1 then 1 else 0 end ) as 迟到人次
COUNT(distinct studentNo) as 在校生人数,
求教
额。。你是不是迟到的太多了把数据范围提高一些
select convert(varchar,convert(decimal(38,2),迟到人次*1.0/在校生人数*100))+'%'
系部,ltrim(Convert(numeric(9,2),迟到人次*100.0/在校生人数))+'%' As 迟到率
From (
... -- 查詢結果
) as t
ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率
From
(
select COUNT(distinct studentNo) as 在校生人数,
SUM(case when late=1 then 1 else 0 end ) as 迟到人数
from stuAttendence
) as t上边的例子用你的方法算出是迟到率是16.25%,但是用临时表的方法算出是10.94%,还有我想请教一下,最后那个as t,t为什么不显示?它是什么?
或者
Select * from tb
Inner join (Select * from tb) As t -- t是別名
SELECT department as 系部,
COUNT(distinct studentNo) as 在校生人数,
SUM(case when late=1 then 1 else 0 end ) as 迟到人次,
ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率
From
(select COUNT(distinct studentNo) as 在校生人数,SUM(case when late=1 then 1 else 0 end ) as 迟到人数 from stuAttendence)--临时表
as t --别名from stuAttendence group by department提示“from附近有语法错误”,是不是两个from的事啊?
再帮我一次好吗?我正在学校做一个比较急的项目,寒假的时候我一定要重新学一下SQL
在校生人数,
迟到人次,
ltrim(Convert(numeric(9,2),迟到人数*100.0/在校生人数))+'%' As 迟到率
From
(select department as 系部,COUNT(distinct studentNo) as 在校生人数,SUM(case when late=1 then 1 else 0 end ) as 迟到人数 from stuAttendence group by department)--临时表
as t --别名