blocks|key|2580235|text|我相信以下内容会奏效，我的知识是使用，而不是MySQL。如果MySQL缺少这些内容，请告诉我，我将删除答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2580236|DECLARE+@Table1+TABLE(
ID+int,
Too+varchar(10),
Fromm+varchar(10),
Compared+int)

INSERT+INTO+@Table1+values+(1,+'John','Mary',+2),+(2,'John',+'Mary',+1),+(3,'Sue','Charles',1),+(4,'Mary','John',3)


SELECT+ID,+Too,+Fromm,+Compared
FROM+@Table1+as+t
INNER+JOIN
(
SELECT
++++CASE+WHEN+Too+<+Fromm+THEN+Too%2BFromm
++++ELSE+Fromm%2BToo
++++END+as+orderedValues,+MIN(compared)+as+minComp
FROM+@Table1
GROUP+BY++++CASE+WHEN+Too+<+Fromm+THEN+Too%2BFromm
ELSE+Fromm%2BToo
END
)+ordered+ON+
ordered.minComp+=+t.Compared+
AND+ordered.orderedValues+=+
++++++++CASE+
++++++++++++WHEN+Too+<+Fromm+
++++++++++++++++THEN+Too%2BFromm
++++++++++++ELSE+
++++++++++++++++Fromm%2BToo
++++++++END|code-block|syntax|javascript|2580237|我使用的是int，而不是时间值，但它的工作原理是一样的。这是肮脏的，但它给了我预期的结果。|2580238|它的基础是使用派生查询，其中您要获取要为其获取唯一值的两列，并使用case语句将它们组合成标准格式。在这种情况下，以前的字母与后面的值按字母顺序连在一起。使用该值获取我们正在寻找的最小值，返回原始表，使值再次分离，再加上该表中的其他内容。它假设我们正在聚合的值将是唯一的，所以在这种情况下，如果有(1，'John'，'Mary'，2)和(2，'Mary'，'John'，2)，它就会打破和返回这对夫妇的2项记录。|2580239|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I believe the following would work, my knowledge is with Microsoft SQL Server, not MySQL. If MySQL lacks one of these, let me know and I'll delete the answer.

<pre><code>DECLARE @Table1 TABLE(
ID int,
Too varchar(10),
Fromm varchar(10),
Compared int)

INSERT INTO @Table1 values (1, 'John','Mary', 2), (2,'John', 'Mary', 1), (3,'Sue','Charles',1), (4,'Mary','John',3)


SELECT ID, Too, Fromm, Compared
FROM @Table1 as t
INNER JOIN
(
SELECT
 CASE WHEN Too &lt; Fromm THEN Too+Fromm
 ELSE Fromm+Too
 END as orderedValues, MIN(compared) as minComp
FROM @Table1
GROUP BY CASE WHEN Too &lt; Fromm THEN Too+Fromm
ELSE Fromm+Too
END
) ordered ON 
ordered.minComp = t.Compared 
AND ordered.orderedValues = 
 CASE 
 WHEN Too &lt; Fromm 
 THEN Too+Fromm
 ELSE 
 Fromm+Too
 END
</code></pre>

I used an int instead of time value, but it would work the same. It's dirty, but it's giving me the results I expected.

The basics of it, is to use a derived query where you take the two columns you want to get unique values for and use a case statement to combine them into a standard format. In this case, earlier alphabetical concatenated with the later value alphabetically. Use that value to get the minimum value we are looking for, join back to the original table to get the values separated out again plus whatever else is in that table. It is assuming the value we are aggregating is going to be unique, so in this case if there was (1, 'John', 'Mary', 2) and (2, 'Mary', 'John', 2), it would kind of break and return 2 records for that couple.

blocks|key|763526|text|SQL小提琴不起作用是因为某些原因，因此，同时，您将需要帮助我来帮助您。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|763527|假设以下语句有效|763528|SELECT+
++++LEAST(to,from)+as+LowVal,+
++++GREATEST(to,from)+as+HighVal,+
++++MAX(date)+as+MaxDate
FROM+table+
GROUP+BY+LEAST(to,from),+GREATEST(to,from)|code-block|syntax|javascript|763529|那你就可以加入|763530|select+t.*
from+
++++table+t
++++inner+join+
++++++++(SELECT+
++++++++++++LEAST(to,from)+as+LowVal,+
++++++++++++GREATEST(to,from)+as+HighVal,+
++++++++++++MAX(date)+as+MaxDate
++++++++FROM+table+
++++++++GROUP+BY+LEAST(to,from),+GREATEST(to,from)
++++++++)+v
++++++++on+t.date+=+v.MaxDate
++++++++and+(t.From+=+v.LowVal+or+t.From+=+v.HighVal)
++++++++and+(t.To+=+v.LowVal+or+t.To=+v.HighVal)|763531|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

SQL fiddle isn't working for some reason so in the mean time you will need to help me to help you.

Assuming that the following statement works

<pre><code>SELECT 
 LEAST(to,from) as LowVal, 
 GREATEST(to,from) as HighVal, 
 MAX(date) as MaxDate
FROM table 
GROUP BY LEAST(to,from), GREATEST(to,from)
</code></pre>

then you could join to that as

<pre><code>select t.*
from 
 table t
 inner join 
 (SELECT 
 LEAST(to,from) as LowVal, 
 GREATEST(to,from) as HighVal, 
 MAX(date) as MaxDate
 FROM table 
 GROUP BY LEAST(to,from), GREATEST(to,from)
 ) v
 on t.date = v.MaxDate
 and (t.From = v.LowVal or t.From = v.HighVal)
 and (t.To = v.LowVal or t.To= v.HighVal)
</code></pre>

blocks|key|2580261|text|这个答案最初是受获取每组成组SQL结果的最大值记录启发的，但后来我进一步研究并想出了正确的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2580262|CREATE+TABLE+T
++++(`id`+int,+`from`+varchar(7),+`to`+varchar(7),+`somedate`+datetime)
;

INSERT+INTO+T
++++(`id`,+`from`,+`to`,+`somedate`)
VALUES
++++(1,+'Number1',+'Number2',+'2015-01-01+00:00:00'),
++++(2,+'Number2',+'Number1',+'2015-01-02+00:00:00'),
++++(3,+'Number2',+'Number1',+'2015-01-03+00:00:00'),
++++(4,+'Number3',+'Number1',+'2015-01-04+00:00:00'),
++++(5,+'Number3',+'Number2',+'2015-01-05+00:00:00');|code-block|syntax|javascript|2580263|在MySQL+5.6.19上测试|2580264|SELECT+*+
FROM+
++++(
++++++++SELECT+*+
++++++++FROM+T+
++++++++ORDER+BY+LEAST(`to`,`from`),+GREATEST(`to`,`from`),+somedate+DESC
++++)+X
GROUP+BY+LEAST(`to`,`from`),+GREATEST(`to`,`from`)|2580265|结果集|style|BOLD|2580266|id+from++++to++++++somedate
3++Number2+Number1+2015-01-03
4++Number3+Number1+2015-01-04
5++Number3+Number2+2015-01-05|2580267|但是，这依赖于MySQL的一些不可靠的行为，这将在将来的版本中被改变。MySQL+5.7+废品查询，因为SELECT子句中的列在功能上不依赖于GROUP列。如果它被配置为接受它(ONLY_FULL_GROUP_BY被禁用)，它的工作方式与以前的版本一样，但仍然不是有保证：“服务器可以自由地从每个组中选择任何值，所以除非它们是相同的，否则所选择的值是不确定的。”|CODE|2580268|因此，正确的答案似乎是：|2580269|SELECT+T.*
FROM+
++++T
++++INNER+JOIN+
++++++++(
++++++++SELECT+
++++++++++++LEAST(`to`,`from`)+AS+LowVal,+
++++++++++++GREATEST(`to`,`from`)+AS+HighVal,+
++++++++++++MAX(somedate)+AS+MaxDate
++++++++FROM+T
++++++++GROUP+BY+LEAST(`to`,`from`),+GREATEST(`to`,`from`)
++++++++)+v
++++++++ON+T.somedate+=+v.MaxDate
++++++++AND+(T.From+=+v.LowVal+OR+T.From+=+v.HighVal)
++++++++AND+(T.To+=+v.LowVal+OR+T.To+=+v.HighVal)|2580270|结果集与上面相同，但在本例中，它保证保持这样，而在您可以轻松获得行Number2,+Number1的不同日期和id之前，这取决于表上有哪些索引。|2580271|它将按预期工作，直到原始数据中有两行具有完全相同的somedate、to和from。|2580272|让我们再添加一行：|2580273|INSERT+INTO+T+(`id`,+`from`,+`to`,+`somedate`)
VALUES+(6,+'Number1',+'Number2',+'2015-01-03+00:00:00');|2580274|上面的查询将返回2015-01-03的两行|2580275|id+from++++to++++++somedate
3++Number2+Number1+2015-01-03
6++Number1+Number2+2015-01-03
4++Number3+Number1+2015-01-04
5++Number3+Number2+2015-01-05|2580276|要解决这个问题，我们需要一个方法来选择组中的一行。在这个例子中，我们可以使用唯一的ID来打破领带。如果组中有多个具有相同最大日期的行，我们将选择ID最大的行。|2580277|名为Groups的最内部子查询只返回所有组，就像问题中的原始查询一样。然后我们向这个结果集添加一个列id，我们选择属于同一组的id，它具有最高的somedate，然后是最高的id，这是由ORDER+BY和LIMIT完成的。这个子查询称为GroupsWithIDs。一旦我们有了每个组的所有组和一个正确行的id，我们就把它放到原始表中，以便为找到的id获取列的其余部分。|2580278|最终查询|2580279|SELECT+T.*
FROM
++++(
++++SELECT
++++++++Groups.N1
++++++++,Groups.N2
++++++++,
++++++++(
++++++++++++SELECT+T.id
++++++++++++FROM+T
++++++++++++WHERE
++++++++++++++++LEAST(`to`,`from`)+=+Groups.N1+AND
++++++++++++++++GREATEST(`to`,`from`)+=+Groups.N2
++++++++++++ORDER+BY+T.somedate+DESC,+T.id+DESC
++++++++++++LIMIT+1
++++++++)+AS+id
++++FROM
++++++++(
++++++++++++SELECT+LEAST(`to`,`from`)+AS+N1,+GREATEST(`to`,`from`)+AS+N2
++++++++++++FROM+T+
++++++++++++GROUP+BY+LEAST(`to`,`from`),+GREATEST(`to`,`from`)
++++++++)+AS+Groups
++++)+AS+GroupsWithIDs
++++INNER+JOIN+T+ON+T.id+=+GroupsWithIDs.id|2580280|最终结果集|2580281|id+from++++to++++++somedate
4++Number3+Number1+2015-01-04
5++Number3+Number2+2015-01-05
6++Number1+Number2+2015-01-03|2580282|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results|1|http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html|2|http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html^0|8|H|0|0|0|0|0|0|3|0|0|2H|I|19|2|1|3O|3|2|0|0|0|X|G|0|P|8|Y|2|11|4|0|0|0|8|A|0|0|15|2|0|2|6|1E|2|1R|2|20|8|2F|2|2L|8|2U|5|3A|D|48|2|4T|2|0|0|4|0|0|0|5|0|0^^$0|@$1|2|3|4|5|6|7|1Z|8|@]|9|@$A|20|B|21|1|22]]|C|$]]|$1|D|3|E|5|F|7|23|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|24|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|25|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|26|8|@$A|27|B|28|O|P]]|9|@]|C|$]]|$1|Q|3|R|5|F|7|29|8|@]|9|@]|C|$G|H]]|$1|S|3|T|5|6|7|2A|8|@$A|2B|B|2C|O|U]]|9|@$A|2D|B|2E|1|2F]|$A|2G|B|2H|1|2I]]|C|$]]|$1|V|3|W|5|6|7|2J|8|@]|9|@]|C|$]]|$1|X|3|Y|5|F|7|2K|8|@]|9|@]|C|$G|H]]|$1|Z|3|10|5|6|7|2L|8|@$A|2M|B|2N|O|U]]|9|@]|C|$]]|$1|11|3|12|5|6|7|2O|8|@$A|2P|B|2Q|O|U]|$A|2R|B|2S|O|U]|$A|2T|B|2U|O|U]]|9|@]|C|$]]|$1|13|3|14|5|6|7|2V|8|@]|9|@]|C|$]]|$1|15|3|16|5|F|7|2W|8|@]|9|@]|C|$G|H]]|$1|17|3|18|5|6|7|2X|8|@$A|2Y|B|2Z|O|U]]|9|@]|C|$]]|$1|19|3|1A|5|F|7|30|8|@]|9|@]|C|$G|H]]|$1|1B|3|1C|5|6|7|31|8|@$A|32|B|33|O|U]]|9|@]|C|$]]|$1|1D|3|1E|5|6|7|34|8|@$A|35|B|36|O|U]|$A|37|B|38|O|U]|$A|39|B|3A|O|U]|$A|3B|B|3C|O|U]|$A|3D|B|3E|O|U]|$A|3F|B|3G|O|U]|$A|3H|B|3I|O|U]|$A|3J|B|3K|O|U]|$A|3L|B|3M|O|U]|$A|3N|B|3O|O|U]]|9|@]|C|$]]|$1|1F|3|1G|5|6|7|3P|8|@$A|3Q|B|3R|O|P]]|9|@]|C|$]]|$1|1H|3|1I|5|F|7|3S|8|@]|9|@]|C|$G|H]]|$1|1J|3|1K|5|6|7|3T|8|@$A|3U|B|3V|O|P]]|9|@]|C|$]]|$1|1L|3|1M|5|F|7|3W|8|@]|9|@]|C|$G|H]]|$1|1N|3|-4|5|6|7|3X|8|@]|9|@]|C|$]]]|1O|$1P|$5|1Q|1R|1S|C|$1T|1U]]|1V|$5|1Q|1R|1S|C|$1T|1W]]|1X|$5|1Q|1R|1S|C|$1T|1Y]]]]

This answer was originally inspired by <a href="https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results">Get records with max value for each group of grouped SQL results</a>
but then I looked further and came up with the correct solution.

<pre><code>CREATE TABLE T
 (`id` int, `from` varchar(7), `to` varchar(7), `somedate` datetime)
;

INSERT INTO T
 (`id`, `from`, `to`, `somedate`)
VALUES
 (1, 'Number1', 'Number2', '2015-01-01 00:00:00'),
 (2, 'Number2', 'Number1', '2015-01-02 00:00:00'),
 (3, 'Number2', 'Number1', '2015-01-03 00:00:00'),
 (4, 'Number3', 'Number1', '2015-01-04 00:00:00'),
 (5, 'Number3', 'Number2', '2015-01-05 00:00:00');
</code></pre>

Tested on MySQL 5.6.19

<pre><code>SELECT * 
FROM 
 (
 SELECT * 
 FROM T 
 ORDER BY LEAST(`to`,`from`), GREATEST(`to`,`from`), somedate DESC
 ) X
GROUP BY LEAST(`to`,`from`), GREATEST(`to`,`from`)
</code></pre>

Result set

<pre class="lang-none prettyprint-override"><code>id from to somedate
3 Number2 Number1 2015-01-03
4 Number3 Number1 2015-01-04
5 Number3 Number2 2015-01-05
</code></pre>

But, this relies on some shady behavior of MySQL, which will be changed in future versions. MySQL 5.7 <a href="http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="nofollow noreferrer">rejects</a> this query because the columns in the SELECT clause are not functionally dependent on the GROUP BY columns. If it is configured to accept it (<code>ONLY_FULL_GROUP_BY</code> is disabled), it works like the previous versions, but still it is not <a href="http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html" rel="nofollow noreferrer">guaranteed</a>: "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So, the correct answer seems to be this:

<pre><code>SELECT T.*
FROM 
 T
 INNER JOIN 
 (
 SELECT 
 LEAST(`to`,`from`) AS LowVal, 
 GREATEST(`to`,`from`) AS HighVal, 
 MAX(somedate) AS MaxDate
 FROM T
 GROUP BY LEAST(`to`,`from`), GREATEST(`to`,`from`)
 ) v
 ON T.somedate = v.MaxDate
 AND (T.From = v.LowVal OR T.From = v.HighVal)
 AND (T.To = v.LowVal OR T.To = v.HighVal)
</code></pre>

Result set is the same as above, but in this case it is guaranteed to stay like this, while before you could easily get different date and id for row <code>Number2, Number1</code>, depending on what indexes you have on the table.

It will work as expected until you have two rows in the original data that have exactly the same <code>somedate</code> and <code>to</code> and <code>from</code>.

Let's add another row:

<pre><code>INSERT INTO T (`id`, `from`, `to`, `somedate`)
VALUES (6, 'Number1', 'Number2', '2015-01-03 00:00:00');
</code></pre>

The query above would return two rows for <code>2015-01-03</code>:

<pre class="lang-none prettyprint-override"><code>id from to somedate
3 Number2 Number1 2015-01-03
6 Number1 Number2 2015-01-03
4 Number3 Number1 2015-01-04
5 Number3 Number2 2015-01-05
</code></pre>

To fix this we need a method to choose only one row in the group. In this example we can use unique <code>ID</code> to break the tie. If there are more than one rows in the group with the same maximum date we will choose the row with the largest ID.

The inner-most sub-query called <code>Groups</code> simply returns all groups, like original query in the question. Then we add one column <code>id</code> to this result set, and we choose <code>id</code> that belongs to the same group and has highest <code>somedate</code> and then highest <code>id</code>, which is done by <code>ORDER BY</code> and <code>LIMIT</code>. This sub-query is called <code>GroupsWithIDs</code>. Once we have all groups and an <code>id</code> of the correct row for each group we <code>join</code> this to the original table to fetch the rest of the column for found <code>id</code>s.

final query

<pre><code>SELECT T.*
FROM
 (
 SELECT
 Groups.N1
 ,Groups.N2
 ,
 (
 SELECT T.id
 FROM T
 WHERE
 LEAST(`to`,`from`) = Groups.N1 AND
 GREATEST(`to`,`from`) = Groups.N2
 ORDER BY T.somedate DESC, T.id DESC
 LIMIT 1
 ) AS id
 FROM
 (
 SELECT LEAST(`to`,`from`) AS N1, GREATEST(`to`,`from`) AS N2
 FROM T 
 GROUP BY LEAST(`to`,`from`), GREATEST(`to`,`from`)
 ) AS Groups
 ) AS GroupsWithIDs
 INNER JOIN T ON T.id = GroupsWithIDs.id
</code></pre>

final result set

<pre class="lang-none prettyprint-override"><code>id from to somedate
4 Number3 Number1 2015-01-04
5 Number3 Number2 2015-01-05
6 Number1 Number2 2015-01-03
</code></pre>

Bellow is an example table.

<pre><code>ID FROM TO DATE 
1 Number1 Number2 somedate
2 Number2 Number1 somedate
3 Number2 Number1 somedate
4 Number3 Number1 somedate
5 Number3 Number2 somedate
</code></pre>

Expected result is to get 1 row for each unique pair of TO and FROM columns

Example result if ordered by ID ASC

<pre><code>(1,Number1,Number2)
(4,Number3,Number1)
(5,Number3,Number2)
</code></pre>

Ok I have found how to do this with the following query

<pre><code>SELECT * FROM table GROUP BY LEAST(to,from), GREATEST(to,from)
</code></pre>

However I am not able to get the most recent record for every unique pair.

I have tried with <code>order by ID desc</code> but it returns the first found row for unique pair.

SQL How to group by two columns

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

贝娄是一个例子表。ID   FROM       TO         DATE  1    Number1    Number2    somedate2    Number2    Number1    somedate3    Number2    Number1    somedate4    Number3 ...

问SQL如何按两列分组
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL如何按两列分组EN