CREATE TABLE ##tempTb([id] VARCHAR(1),[value] float(8))
INSERT ##tempTb
SELECT 'A' ,40
UNION ALL SELECT 'B' ,30
UNION ALL SELECT 'C' ,10
UNION ALL SELECT 'D' ,6
UNION ALL SELECT 'E' ,5
UNION ALL SELECT 'F' ,4
UNION ALL SELECT 'G' ,2
UNION ALL SELECT 'H' ,2
UNION ALL SELECT 'I' ,1
UNION ALL SELECT 'J' ,0--这个表是数据库中查出来每一项发生的概率
Select * from ##tempTbdeclare @i int,@count int,@tempValue float;
declare @finalId VARCHAR(1);set @i=1;
set @tempValue=0;
select @count=COUNT(*) From ##tempTb;CREATE TABLE ##finalTb([id] VARCHAR(1),[value] float(8))While @i<=@count
Begin
Select @finalId=T.id, @tempValue= @tempValue+T.value
From
(Select row_number()over(order by value desc)RowNo,id,value from ##tempTb) T
Where T.RowNo = @i;
INSERT ##finalTb
Select @finalId,@tempValue
Set @i=@i+1
End
--输出排列图累计概率
Select * From ##finalTbDROP TABLE ##tempTb
DROP TABLE ##finalTb
排列图需要的累计概率,数据库最初得到的只是每一项的发生概率
现在我是通过循环相加得到累计概率的
请问还有没有更加简单的方法?
INSERT ##tempTb
SELECT 'A' ,40
UNION ALL SELECT 'B' ,30
UNION ALL SELECT 'C' ,10
UNION ALL SELECT 'D' ,6
UNION ALL SELECT 'E' ,5
UNION ALL SELECT 'F' ,4
UNION ALL SELECT 'G' ,2
UNION ALL SELECT 'H' ,2
UNION ALL SELECT 'I' ,1
UNION ALL SELECT 'J' ,0--这个表是数据库中查出来每一项发生的概率
Select * from ##tempTbdeclare @i int,@count int,@tempValue float;
declare @finalId VARCHAR(1);set @i=1;
set @tempValue=0;
select @count=COUNT(*) From ##tempTb;CREATE TABLE ##finalTb([id] VARCHAR(1),[value] float(8))While @i<=@count
Begin
Select @finalId=T.id, @tempValue= @tempValue+T.value
From
(Select row_number()over(order by value desc)RowNo,id,value from ##tempTb) T
Where T.RowNo = @i;
INSERT ##finalTb
Select @finalId,@tempValue
Set @i=@i+1
End
--输出排列图累计概率
Select * From ##finalTbDROP TABLE ##tempTb
DROP TABLE ##finalTb
排列图需要的累计概率,数据库最初得到的只是每一项的发生概率
现在我是通过循环相加得到累计概率的
请问还有没有更加简单的方法?
INSERT ##tempTb
SELECT 'A' ,40
UNION ALL SELECT 'B' ,30
UNION ALL SELECT 'C' ,10
UNION ALL SELECT 'D' ,6
UNION ALL SELECT 'E' ,5
UNION ALL SELECT 'F' ,4
UNION ALL SELECT 'G' ,2
UNION ALL SELECT 'H' ,2
UNION ALL SELECT 'I' ,1
UNION ALL SELECT 'J' ,0
--这个表是数据库中查出来每一项发生的概率
Select * from ##tempTb
declare @i int,@count int,@tempValue float;
declare @finalId VARCHAR(1);
set @i=1;
set @tempValue=0;
select @count=COUNT(*) From ##tempTb;
CREATE TABLE ##finalTb([id] VARCHAR(1),[value] float(8))
While @i<=@count
Begin
Select @finalId=T.id, @tempValue= @tempValue+T.value
From
(Select row_number()over(order by value desc)RowNo,id,value from ##tempTb) T
Where T.RowNo = @i;
INSERT ##finalTb
Select @finalId,@tempValue
Set @i=@i+1
End
--输出排列图累计概率
SELECT a.id,(SELECT SUM(value) VALUE FROM (SELECT ROW_NUMBER()OVER(ORDER BY id DESC )oid,* FROM ##tempTb)b WHERE a.oid<=b.oid) value
FROM (SELECT ROW_NUMBER()OVER(ORDER BY id DESC )oid,* FROM ##tempTb) a
ORDER BY a.id
Select * From ##finalTb
--DROP TABLE ##tempTb
--DROP TABLE ##finalTb/*
id value
---- ----------------------
A 40
B 70
C 80
D 86
E 91
F 95
G 97
H 99
I 100
J 100(10 行受影响)
*/
我就知道应该有好的方法。只是没想到用sum