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屋！