行值的总和明确 [英] sum of row value date wise

问题描述

create table Table1
(
TB1_ID varchar(100),
TB1_DT varchar(100),
TB1_PRC varchar(100),
TB1_US varchar(100)
)

select * from Table1

和

and

my table is:
TB1_ID  TB1_DT        TB1_PRC   TB1_US

1	2/8/2013	100	60
2	5/8/2013	1000	600
3	7/8/2013	11000	6100
4	8/8/2013	12000	6200
5	9/8/2013	13000	6300

i希望从 TB1_DT 5/8/2013 到 10/8/2013获得 TB1_PRC 的平均值（对于很多值） 。

但是如何.........

i want to get average(for many values) of TB1_PRC from TB1_DT 5/8/2013 to 10/8/2013.
but how.........

推荐答案

尝试：

SELECT AVG(TB1_PRC) FROM Table1 WHERE TB1_DT BETWEEN '2013-08-05' AND '2013-08-10'

Maciej Los已经在答案中发表评论行日值明智之和：

保罗，请看一下数据库的结构......所有字段都是varchar数据类型。对此有点评论？





Maciej是绝对正确的 - 如果你的话，它将无法正常工作以文本形式存储您的日期：所以不要。始终以最合适的格式存储数据：对于DateTime或DateTime2的日期，因为将它们保持为任何其他格式会让您头疼。文本是一个特别的选择两个主要原因：

1）排序顺序错误：

"Maciej Los has posted a comment to the Answer "sum of row value date wise":
Paul, please have a look at the structure of database... All fields are varchar data types. A bit of comment on that?
"

Maciej is absolutely right - it won't work as given if you store your dates in text form: so don't. Always store data in the most appropriate format: for dates that is DateTime or DateTime2, becaue keeping them in any other format is going to give you headaches. Text is a particularly poors choice for two main reasons:
1) The sort order is wrong:

1/12/2013
2/12/2012
28/12/2013
3/12/2013

2）文本日期意味着它们以某种形式从不同的系统或系统输入文本 - 可能并非全部共享相同的区域设置，因此具有不同的日期格式：您将如何应对

2) Text dates imply that they are entered as text in some form from a different system or systems - which may not all share the same locale and thus have different date formats: how are you going to cope with

1/12/2013
12/1/2013
2013-12-1
13/12/1
12-1-13
1st Dec '13

在日期字段中存储日期解决了这些问题，前提是它们作为DateTime值而不是字符串传递给SQL - 如同反正应该是为了防止SQL注入攻击。



更改数据库，然后使用上面的代码。

Storing dates in a date field solves these problems provided they are passed to SQL as DateTime values rather than strings - as they should be anyway to prevent SQL Injection attacks.

Change your database, then use the code above.

OriginallGriff的答案是非常好，但我建议用适当的数据类型创建表：

OriginallGriff's answer is very good, but i would suggest to create table with proper data types:

create table Table1
(
TB1_ID INT IDENTITY(1,1),
TB1_DT DATE --or DATETIME or SMALLDATETIME,
TB1_PRC INT,
TB1_US INT
)

有关更多信息，请参阅： T-SQL数据类型 [ ^ ]

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