初学写存储过程,请问这个存储过程如何写?

Question

上面的 ID 是怎么回事?
  是 '1 cgb' 表示ID吗? 还是 1?

Answer 1

每条记录的Time都是一个时间差来的,这个时间差来自另外两个时间相减,
ID      T1    T2   Time       Money
---------------------------------------
1 cgb   (datetime) T2-T1      1,555.56 
2 cgb              T2-T1       888.89 
3 cgb              T2-T1      2,222.22 
4 cgb              T2-T1      1,333.33 
5 cgb              T2-T1      1,333.33 
6 cgb              T2-T1      1,333.33 
7 cgb              T2-T1       3,636.36 
8 cgb              T2-T1      6,363.64 
9 Thomas           T2-T1     1,333.33 要求变成这样
ID         Time       Money
--------------------------------
1 cgb      总时间和   总时间和
2 Thomas   总时间和   总时间和

Answer 2

ID  Name      T1    T2   Time       Money
---------------------------------------
1  cgb   (datetime)      T2-T1      1,555.56 
2  cgb                   T2-T1       888.89 
3  cgb                   T2-T1      2,222.22 
4  cgb                   T2-T1      1,333.33 
5  cgb                   T2-T1      1,333.33 
6  cgb                   T2-T1      1,333.33 
7  cgb                   T2-T1       3,636.36 
8  cgb                   T2-T1      6,363.64 
9  Thomas                T2-T1     1,333.33 ID  Name       Time       Money
--------------------------------
1   cgb      总时间和    总钱和
2   Thomas   总时间和    总钱和

Answer 3

select Name,sum(datediff(mi,t1,t2)) as Time,sum(Money) as Money
from tablename
group by Name这样吗？
如果是这样的话根本用不着存储过程阿

Answer 4

select  identity(int,1,1) name,[time]=sum(t2 - t1),[money]=sum([money]) into #b from tb group by name
select * from #b

Answer 5

select  identity(int,1,1)ID,Name,Sum(Time)总时间和,Sum(Money)总钱和
into #t 
from  table
group by Name

Answer 6

select min(id),
       name,
       sc = convert(varchar(10),sum(convert(int,substring(sc,1,2)))+floor(sum(convert(int,substring(sc,4,2)))/60)+floor(sum(convert(int,substring(sc,7,2)))/3600)) +
            ':'+ convert(varchar(10),sum(convert(int,substring(sc,4,2)))%60+floor(sum(convert(int,substring(sc,7,2)))/60)) + 
            ':'+ replicate('0',2 - len(convert(varchar(2),sum(convert(int,substring(sc,7,2)))%60)))+convert(varchar(2),sum(convert(int,substring(sc,7,2)))%60),
       sum(price)
from t 
group by name
如果算秒,用上面这个,如果没有,用下面的select min(id),
       name,
       sc = convert(varchar(10),sum(convert(int,substring(sc,1,2)))+floor(sum(convert(int,substring(sc,4,2)))/60)) +
            ':'+ convert(varchar(10),sum(convert(int,substring(sc,4,2)))%60) + 
            ':00',
       sum(price)
from t
group by name

Answer 7

sc 是 Time 列,看你的意思应该是字符串类型
直接用t1,t2 列也可以
select min(id),
       name,
       time = convert(varchar(10),floor(datediff(ss,t1,t2)/3600))+':'+
              convert(varchar(2),floor(floor(datediff(ss,t1,t2)%3600)/60))+':'+
              convert(varchar(2),floor(floor(datediff(ss,t1,t2)%3600)%60)),
       sum(price)
from t
group by name

Answer 8

非常感谢jiushaoye(打倒小日本)