SQL查询求和nvarchar值 [英] SQL Query to sum nvarchar value

问题描述

您好



我有一张如下表格





Id int



价值nvarchar



表中的数值如下所示



ID价值

1 10

2 20

3 30

4 9:15

5 10:15

6 8:10

7 9:15

8 40

9 50



在上表中，Value包含int和time。

现在我需要sql查询到总结所有的t值和时间值



请帮我看看这个..



谢谢

Mohan

Hi

I have a table like below


Id int

Value nvarchar

Values in the table are like below

ID Value
1 10
2 20
3 30
4 9:15
5 10:15
6 8:10
7 9:15
8 40
9 50

In above table Value contains both int and time .
Now i need the sql query to sum all the in t values and Time values

Please help me to achive this ..

Thanks
Mohan

推荐答案

试试这个



添加整数值：

Try this

to add integer value :

select sum(cast(value as float))From ##table where not value like '%:%'

添加时间值

To add Time value

select convert(time,dateadd(s,SUM(
( DATEPART(hh, value) * 3600 ) +
( DATEPART(mi, value) * 60 ) +
 DATEPART(ss, value)),0)) From ##table where value like '%:%'

另一个解决方案是：

Another solution is:

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), Value VARCHAR(10))

INSERT INTO @tmp (Value)
VALUES('10'),('20'),('30'),('9:15'),('10:15'),
        ('8:10'),('9:15'),('40'),('50')


SELECT DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', T1.TValue )), '00:00:00.000') AS SumOfTime
FROM (
    SELECT ID, Value AS TValue
    FROM @tmp
    WHERE CHARINDEX(':', Value)>0
    ) AS T1


SELECT SUM(CONVERT(INT,T2.IValue)) AS SumOfInt
FROM (
    SELECT ID, Value AS IValue
    FROM @tmp
    WHERE CHARINDEX(':', Value)=0
    ) AS T2

结果：

Results:

SumOfTime
1900-01-02 12:55:00.000  - it means 2 days and 12 hrs 55 mins

SumOfInt
150

你可以这样做



这将给你所有整数的总和

you can do it like this

this will give you sum of all integers

SELECT Sum(value)
    FROM YourTable
    WHERE ISNUMERIC(value + '.0e0') = 1

这将给你所有时间的总和

this will give you sum of all time

SELECT Sum(convert(time,value))
    FROM YourTable
     WHERE ISNUMERIC(value + '.0e0') != 1

这篇关于SQL查询求和nvarchar值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！