Sql查询返回基于两列的整理结果 [英] Sql query to return collated results based upon two columns
问题描述
假设我有一个包含以下列的表:
Suppose I have a table with the following columns:
Id, ConnectionId, File, FileCreated
int, int, varchar, DateTime
数据示例
此外,假设它是充满了如下未分类的数据:
Data Example
Further, suppose it is filled with unsorted data like the following:
1, 3, "a.dat", 2018-05-16 13:53:40.008
2, 1, "b.dat", 2018-05-16 13:53:40.007
3, 3, "c.dat", 2018-05-16 13:53:40.009
4, 3, "z.dat", 2018-05-16 13:53:40.002
5, 3, "h.dat", 2018-05-16 13:53:40.003
6, 2, "ba.dat", 2018-05-16 13:53:40.004
7, 3, "zy.dat", 2018-05-16 13:53:40.005
8, 1, "f.dat", 2018-05-16 13:53:40.001
9, 1, "cd.dat", 2018-05-16 13:53:40.006
我如何查询这些数据,以便数据集返回给我:
1.每个连接一行
2.按时间升序订购
Ex结果
我正在寻找的结果如下:
(注意:我将这些放在组中,这样你就可以看到它们首先根据ConnectionId返回(也许就像是group by))。
How might I query this data so that the dataset returns me :
1. one row for each of the connections
2. ordered in time ascending order
Expected Results
The result I'm looking for would be the following:
(Note: I'm putting these in groups so you can see that they are returned based upon ConnectionId first (like group by maybe) ).
8, 1, "f.dat", 2018-05-16 13:53:40.001
4, 3, "z.dat", 2018-05-16 13:53:40.002
6, 2, "ba.dat", 2018-05-16 13:53:40.004
5, 3, "h.dat", 2018-05-16 13:53:40.003
9, 1, "cd.dat", 2018-05-16 13:53:40.006
7, 3, "zy.dat", 2018-05-16 13:53:40.005
2, 1, "b.dat", 2018-05-16 13:53:40.007
1, 3, "a.dat", 2018-05-16 13:53:40.008
3, 3, "c.dat", 2018-05-16 13:53:40.009
希望我的例子告诉你这些不是严格基于时间的,也不是严格基于connectionId的。这两个。
我尝试过:
Hopefully my example shows you that these are not strictly time-based and not strictly connectionId based either. It is both.
What I have tried:
select connectionid, fileCreated from Table
group by connectionid, filecreated
只返回3行,但有9行,所以我错了。
Returns only 3 rows but there are 9 rows so mine is wrong.
推荐答案
好的,另一个人在另一个网站解决了这个。
我非常惊讶,因为提供的答案给了我我要求的精确结果。哇!!这是一个非常有趣的查询。
这是答案的样子:
Okay, someone at another site solved this.
I am absolutely amazed because the answer that was provided gave me the EXACT results that I asked for. Wow!! It's quite an interesting query.
Here's what the answer looked like:
DECLARE @mockup TABLE(Id INT,ConnectionId INT,[File] VARCHAR(100),FileCreated DATETIME2);
INSERT INTO @mockup VALUES
(1, 3, 'a.dat','2018-05-16T13:53:40.008')
,(2, 1, 'b.dat','2018-05-16T13:53:40.007')
,(3, 3, 'c.dat','2018-05-16T13:53:40.009')
,(4, 3, 'z.dat','2018-05-16T13:53:40.002')
,(5, 3, 'h.dat','2018-05-16T13:53:40.003')
,(6, 2, 'ba.dat','2018-05-16T13:53:40.004')
,(7, 3, 'zy.dat','2018-05-16T13:53:40.005')
,(8, 1, 'f.dat','2018-05-16T13:53:40.001')
,(9, 1, 'cd.dat','2018-05-16T13:53:40.006');
SELECT *
FROM @mockup
ORDER BY ROW_NUMBER() OVER(PARTITION BY ConnectionId ORDER BY FileCreated)
,FileCreated;
结果如下所示:
The results looked like this:
Id ConnectionId File FileCreated
8 1 f.dat 2018-05-16 13:53:40.0010000
4 3 z.dat 2018-05-16 13:53:40.0020000
6 2 ba.dat 2018-05-16 13:53:40.0040000
5 3 h.dat 2018-05-16 13:53:40.0030000
9 1 cd.dat 2018-05-16 13:53:40.0060000
7 3 zy.dat 2018-05-16 13:53:40.0050000
2 1 b.dat 2018-05-16 13:53:40.0070000
1 3 a.dat 2018-05-16 13:53:40.0080000
3 3 c.dat 2018-05-16 13:53:40.0090000
这篇关于Sql查询返回基于两列的整理结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!