none
SQL在查询时候数据换转问题 RRS feed

  • 问题

  • 有这样一列int数据类型 在查询的时候让他和一个数相除,结果是浮点数据类型,知道有一个数据类型转换函数,我想让2列数据同时并存,改如何写SQL语句呢?
    查询结果应该是这样!

    number
     15
    查询15/2的结果和原来列中的数据
    number   result
     15           7.5
    2009年11月19日 7:53

答案

  • select scom,SUM(cEffect) as total,sum(iLength) as leng,CONVERT(float,leng)/60 as result  from tLogs group by sCom

    消息 207,级别 16,状态 1,第 1 行
    列名 'leng' 无效。

    别名在转换函数中引用无效吗?

    select scom,SUM(cEffect) as total,sum(iLength) as leng,sum(iLength)*1.0/60 as result  from tLogs group by sCom

    or


    select scom,SUM(cEffect) as total,sum(iLength) as leng,cast(sum(iLength) *1.0/60 as as numeric(12,1)) as result  from tLogs group by sCom

    or

    ;with t
    as
    (select scom,SUM(cEffect) as total,sum(iLength) as leng,from tLogs group by sCom
    )
    select *,cast(sum(iLength)*1.0/60  as numeric(12,1)) as result  
    from t


    More: blog.csdn.net/happyflystone
    2009年11月19日 10:05

全部回复

  • select number, convert(float, number) /2 as result ...
    想不想时已是想,不如不想都不想。
    2009年11月19日 8:58
    版主
  • select scom,SUM(cEffect) as total,sum(iLength) as leng,CONVERT(float,leng)/60 as result  from tLogs group by sCom

    消息 207,级别 16,状态 1,第 1 行
    列名 'leng' 无效。

    别名在转换函数中引用无效吗?
    2009年11月19日 9:50
  • select scom,SUM(cEffect) as total,sum(iLength) as leng,CONVERT(float,leng)/60 as result  from tLogs group by sCom

    消息 207,级别 16,状态 1,第 1 行
    列名 'leng' 无效。

    别名在转换函数中引用无效吗?

    select scom,SUM(cEffect) as total,sum(iLength) as leng,sum(iLength)*1.0/60 as result  from tLogs group by sCom

    or


    select scom,SUM(cEffect) as total,sum(iLength) as leng,cast(sum(iLength) *1.0/60 as as numeric(12,1)) as result  from tLogs group by sCom

    or

    ;with t
    as
    (select scom,SUM(cEffect) as total,sum(iLength) as leng,from tLogs group by sCom
    )
    select *,cast(sum(iLength)*1.0/60  as numeric(12,1)) as result  
    from t


    More: blog.csdn.net/happyflystone
    2009年11月19日 10:05
  • 非常感谢各位的回答,为什么只有在乘于1.0后,后面的小数才会出来呢?
    2009年11月19日 10:29
  • Sql uses int as default type if you don't specify one, times 1.0 casts to numeric type.
    2009年11月19日 16:29