求乘积

lnming 2010-08-02 09:27:56
Series Inv ABC
1 1 26
1 1 15
1 1 37
1 1 26
1 1 32
1 1 23
1 2 29
1 2 20
1 2 23
1 2 29
1 2 23
1 2 28
1 3 27
1 3 26
1 3 26
1 3 27
1 3 26
1 3 25
1 4 30
1 4 26
1 4 29
1 4 27
1 4 24
1 4 29
1 5 21
1 5 15
1 5 29
1 5 34
1 5 29
1 5 20
这是数据库的值
SELECT 974as Series,1 as Inv,
POWER(10.00000000,SUM(LOG10([01])))/100000000 AS '01',
POWER(10.00000000,SUM(LOG10([02])))/100000000 AS '02',
POWER(10.00000000,SUM(LOG10([03])))/100000000 AS '03',
POWER(10.00000000,SUM(LOG10([04])))/100000000 AS '04',
POWER(10.00000000,SUM(LOG10([05])))/100000000 AS '05',
POWER(10.00000000,SUM(LOG10([06])))/100000000 AS '06',
POWER(10.00000000,SUM(LOG10([07])))/100000000 AS '07',
POWER(10.00000000,SUM(LOG10([08])))/100000000 AS '08',
POWER(10.00000000,SUM(LOG10([09])))/100000000 AS '09',
POWER(10.00000000,SUM(LOG10([10])))/100000000 AS '10',
POWER(10.00000000,SUM(LOG10([11])))/100000000 AS '11',
POWER(10.00000000,SUM(LOG10([12])))/100000000 AS '12',
POWER(10.00000000,SUM(LOG10([13])))/100000000 AS '13',
POWER(10.00000000,SUM(LOG10([14])))/100000000 AS '14',
POWER(10.00000000,SUM(LOG10([15])))/100000000 AS '15',
POWER(10.00000000,SUM(LOG10([16])))/100000000 AS '16',
POWER(10.00000000,SUM(LOG10([17])))/100000000 AS '17',
POWER(10.00000000,SUM(LOG10([18])))/100000000 AS '18',
POWER(10.00000000,SUM(LOG10([19])))/100000000 AS '19',
POWER(10.00000000,SUM(LOG10([20])))/100000000 AS '20',
POWER(10.00000000,SUM(LOG10([21])))/100000000 AS '21',
POWER(10.00000000,SUM(LOG10([22])))/100000000 AS '22',
POWER(10.00000000,SUM(LOG10([23])))/100000000 AS '23',
POWER(10.00000000,SUM(LOG10([24])))/100000000 AS '24',
POWER(10.00000000,SUM(LOG10([25])))/100000000 AS '25',
POWER(10.00000000,SUM(LOG10([26])))/100000000 AS '26',
POWER(10.00000000,SUM(LOG10([27])))/100000000 AS '27',
POWER(10.00000000,SUM(LOG10([28])))/100000000 AS '28',
POWER(10.00000000,SUM(LOG10([29])))/100000000 AS '29',
POWER(10.00000000,SUM(LOG10([30])))/100000000 AS '30',
POWER(10.00000000,SUM(LOG10([31])))/100000000 AS '31',
POWER(10.00000000,SUM(LOG10([32])))/100000000 AS '32',
POWER(10.00000000,SUM(LOG10([33])))/100000000 AS '33'
FROM TRedFollowInvGroup WHERE Series = 974 and Inv = 1
这是我的SQL语句
截了一部分的数据，要求的是某一Series的ABC字段的连乘。但是直接连乘的话，乘积过大，无法正常显示，所以采用一个INV值连乘一次，然后再除到一两位数，再连乘每个IVN得出的乘积。但感觉自己的SQL语句不过关，因为这样的话，要多次调用SQL语句，多少个INV值就是调用多少次，然后又要再连乘，不知道有没有一个SQL语句就搞定了的。最后是显示除了N倍后的数值。
...全文
135 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
hokor 2010-08-02
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 lnming 的回复:]
我的数据实际每个序号有198行，我取这个乘积是用于比较，所以可以除了10的N倍，只要正常显示，小数点长点都没关系。
[/Quote]不是10的N倍式10的N次方。10的200次方大概等于2的600到800次方之间吧。。这个数不好计算机不好处理啊，比天上的星星还多了吧，把全世界人的手指头加脚趾头都用上也不够使啊|!!
SQLCenter 2010-08-02
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 lnming 的回复:]

我的数据实际每个序号有198行，我取这个乘积是用于比较，所以可以除了10的N倍，只要正常显示，小数点长点都没关系。
[/Quote]

那这样就行了：

exp(sum(log(abc/10.0)))
POWER(10.00000000,SUM(LOG10(ABC/10.0)))
lnming 2010-08-02
  • 打赏
  • 举报
 回复
我的数据实际每个序号有198行，我取这个乘积是用于比较，所以可以除了10的N倍，只要正常显示，小数点长点都没关系。
SQLCenter 2010-08-02
  • 打赏
  • 举报
 回复 
--> 测试数据：#

if object_id('tempdb.dbo.#') is not null drop table #

create table #(Series int, Inv int, ABC int)

insert into #

select 1, 1, 26 union all

select 1, 1, 15 union all

select 1, 1, 37 union all

select 1, 1, 26 union all

select 1, 1, 32 union all

select 1, 1, 23 union all

select 1, 2, 29 union all

select 1, 2, 20 union all

select 1, 2, 23 union all

select 1, 2, 29 union all

select 1, 2, 23 union all

select 1, 2, 28 union all

select 1, 3, 27 union all

select 1, 3, 26 union all

select 1, 3, 26 union all

select 1, 3, 27 union all

select 1, 3, 26 union all

select 1, 3, 25 union all

select 1, 4, 30 union all

select 1, 4, 26 union all

select 1, 4, 29 union all

select 1, 4, 27 union all

select 1, 4, 24 union all

select 1, 4, 29 union all

select 1, 5, 21 union all

select 1, 5, 15 union all

select 1, 5, 29 union all

select 1, 5, 34 union all

select 1, 5, 29 union all

select 1, 5, 20





select Series, exp(sum(log(abc)))abc from # group by Series

/*

Series      abc

----------- ----------------------

1           1.68743027138102E+42

*/



select Series, POWER(cast(10 as float),SUM(LOG10(ABC)))abc from # group by Series

/*

Series      abc

----------- ----------------------

1           1.68743027138102E+42

*/


如果超出float的范围，就无法计算了。

POWER(10.00000000... 按numeric类型计算。
黄_瓜 2010-08-02
  • 打赏
  • 举报
 回复 
--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([Series] int,[Inv] int,[ABC] int)

insert [tb]

select 1,1,26 union all

select 1,1,15 union all

select 1,1,37 union all

select 1,1,26 union all

select 1,1,32 union all

select 1,1,23 union all

select 1,2,29 union all

select 1,2,20 union all

select 1,2,23 union all

select 1,2,29 union all

select 1,2,23 union all

select 1,2,28 union all

select 1,3,27 union all

select 1,3,26 union all

select 1,3,26



declare @i decimal(38,0)

--38位不短了吧？

set @i=1

select @i=@i*[ABC] from [tb] 

select @i

/*

---------------------------------------- 

1255647245683280486400



（所影响的行数为 1 行）



*/
c++矩阵行列式，转置，乘积，求逆
c++代码 矩阵行列式，转置，乘积，求逆 高斯约旦法，行列式法求解逆矩阵 一部分自己写的
[office] excel求乘积的公式和方法 #媒体#笔记#经验分享
B2单元格为：=LOOKUP(9^99,--LEFT(A2,ROW($1:$100)))*LOOKUP(9^99,--LEFT(RIGHT(A2,LEN(A2)-(FIND("*",A2,1))),ROW($1:$100)))&"元"本文首先给出两个常规的excel求乘积的链接，然后再例举了一个文字和数字在同一...
Java_题目_两个字符串求乘积
Java_题目_两个字符串求乘积
mysql添加一列求乘积_MySQL 生成累计乘积
需求计算某给数字列的累乘积。其操作方式与“计算累计和”相似，只是使用乘法而不是加法。二.解决方案作为例子，本解决方案中都计算职员工资的累成绩。虽然工资的累成绩没有多大用处，然而可以很容易地把该技巧用于...
用c语言编写两整数乘积,c语言两个数相乘求积 c语言输入两个整数求乘积
思路：double类型的范围： -1.7*10(-308)～1.7*10(308)，如果两个数的乘积不超过这样的精度，可以直接使用*符号进行运算。 参考代码： #include用C语言编写两个数求和求积求商？利用C语言编写一个求两数相加的和的...
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章