Sql查询获取实例的b / w max和min的差异 [英] Sql query to get the difference b/w max and min of an instance

问题描述

我期待一个查询列出所有记录，其中包含该用户的最大和最小时间差异



见下文



当前格式

I am expecting a query to list out all the records with the difference of Max and Min time of that user

See below

Current Format

id......Name....Time..................Expected column(In secs)
1  | 	John  | 2015-11-29 14:01:16  | 	7
2  |  	John | 	2015-11-29 14:01:16  | 	7
3  | 	John | 	2015-11-29 14:01:22 |  	7
4  | 	John | 	2015-11-29 14:01:22  | 	7	
5  | 	Mark | 	2015-11-29 14:00:54  | 	6
6 | 	Mark | 	2015-11-29 14:00:54  | 	6
7 | 	Mark | 	2015-11-29 14:01:00  | 	6
8 | 	Mark | 	2015-11-29 14:01:00  | 	6

在上表中我要求的预期栏目..



在上表中我想添加一个列预期列，我想要计算的是ve ve





预期栏目公式



Perosn的差异（最长（时间） - 分钟（时间））


让我们为John做个例子

In the above table Expected column in my requirement..

In the above table I would Like to add a column "Expected column", what i want to calculate is wil be velow


Expected column formula

Diff(Max(Time)-Min(Time)) for that Perosn

Lets take an example for John

Max(Time) is "2015-11-29 14:01:22"
Min(Time) is "2015-11-29 14:01:16"

差异计算为7秒

因此对于约翰的所有记录，时间差异应该是附加的。和其他记录一样..



如果有人能为我提供完美的查询，那真的很棒



我尝试了什么：



我试图通过计算实例的最大和最小时间戳来添加列应该显示该记录的秒数差异。在输出中我应该获得所有带有添加列的记录，显示该实例的时差

Diff is calculated as 7 seconds
So for all the records of John The time diffrence should be apended. Same like other records..

It would be really great if any one provide me the perfect query for this

What I have tried:

I am trying to add a column by calculating the max and min time stamp of an instance and difference of seconds should be displayed for that records.. In out put I should be getting all the recordsss with added column which show the time difference for that instance

推荐答案

关闭，你的表设计看起来很糟糕 - 你不应该每次都使用文本名称，而是使用具有名称和ID值的单独用户表 - 然后使用JOIN关联这两个表。这样，你可以让多个用户拥有相同的名字......：笑：



其次，向每一行添加摘要信息是一种不好的方法 - 它复制工作并复制数据，因此尽管有可能最好有一个摘要查询并将其单独用于您的用户记录。 （复制数据总是一个坏主意！）



但是你可以做到 - 这只是一个坏主意：

First off, your table design looks bad - you shouldn't be using text names each time, but a separate "users" table that has a name, and an ID value - you then correlate the two tables using a JOIN. That way, you can have multiple users with the same first name... :laugh:

Secondly, adding summary info to every row is a bad way to do it - it duplicates work and duplicates data, so while it is possible it's much better to have a summary query and use that separately to your user records. (Duplicating data is always a bad idea!)

But you can do it - it's just a bad idea:

SELECT Id, a.[name], a.[Time], DATEDIFF(ss, b.Mind, b.Maxd) FROM MyTable a
JOIN (SELECT [name], MAX([Time]) AS Maxd, MIN([Time]) AS Mind FROM MyTable
      GROUP BY [name]) b
ON a.[name] = b.[name]

这篇关于Sql查询获取实例的b / w max和min的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！