怎么合计? [英] How to make total?

问题描述

使用 SQLSERVER 2000

Using SQLSERVER 2000

银泰如何做总价值

Table 1
    InTime
    02:00:48
    22:00:22
    .....,

银泰数据类型为 varchar

Intime Datatype is varchar

02:00:12 - (HH:MM:SS)

在我尝试访问 2003 之前

Before I tried in access 2003

select format( Int(24*sum(Intime)), '0') & format( sum(Intime) , ':ss' ) AS totaltime from table1

Above Query 在 Access 2003 中完美运行

Above Query is working perfectly in Access 2003

如何在sql中做总银泰?

How to make a total of Intime in sql?

预期输出

Intime

24:01:00

等等...,

需要查询帮助

推荐答案

试试这个:

CREATE TABLE #Table1
(
inTime varchar(8)
)

SET NOCOUNT ON
INSERT INTO #Table1 VALUES('02:00:48')
INSERT INTO #Table1 VALUES('22:00:22')
SET NOCOUNT OFF


SELECT
    CONVERT(varchar(2),TotalSeconds/3600)
        +':'
        +RIGHT('00'+CONVERT(varchar(2),(TotalSeconds-(TotalSeconds/3600*3600))/60),2)
        +':'
        +RIGHT('00'+CONVERT(varchar(2),TotalSeconds-((TotalSeconds/3600*3600)+(((TotalSeconds-(TotalSeconds/3600*3600))/60)*60))),2) AS Answer
        ,DATEADD(second,dt.TotalSeconds,CONVERT(datetime,'1/1/1900')) AS AnswerIncrementingDays

    FROM (SELECT
              SUM(DATEDIFF(second,CONVERT(datetime,'1/1/1900'),CONVERT(datetime,'1/1/1900 '+inTime))) AS TotalSeconds
              FROM #Table1
         ) dt

输出:

Answer   AnswerIncrementingDays
-------- -----------------------
24:01:10 1900-01-02 00:01:10.000

(1 row(s) affected)

这篇关于怎么合计?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！