SQL如何按两列分组

Question

下面是一个例子表格。

ID   FROM       TO         DATE  
1    Number1    Number2    somedate
2    Number2    Number1    somedate
3    Number2    Number1    somedate
4    Number3    Number1    somedate
5    Number3    Number2    somedate

期望的结果是对每个唯一的TO和FROM列组合获取1行。

如果按照ID升序排序，则示例结果如下：

(1,Number1,Number2)
(4,Number3,Number1)
(5,Number3,Number2)

好的，我找到了如何使用以下查询进行操作。

SELECT * FROM table GROUP BY LEAST(to,from), GREATEST(to,from)

然而，我无法获取每个唯一对的最新记录。

我尝试使用order by ID desc，但它返回唯一对的第一行记录。

- Vladimir Gatev

---

这似乎是一个关于排除镜像结果的问题。 - Elk

我认为你想要的是MySQL中的排名函数，我正在尝试打开SQLFiddle，但由于某种原因它一直在加载。 - J.S. Orris

你说的“每个唯一对应的最新记录”是什么意思？如果它们是唯一的，它们都是最后和最近的。你真的只想获取一条记录吗？ - Alex

根据提供的信息，似乎From = Num1，To = Num2等价于From = Num2，To = Num1...即无论它们在哪一列中，您都在寻找这些对 - 请确认。 - G B

这两篇文章来自Baron Schwartz（也称为VivdCortex的xaprb），可能会很有用。其中一篇介绍了如何在SQL中选择每个组的第一个/最小/最大行，另一篇介绍了如何选择第N个最大/最小/第一个/最后一个行。 - fenway

Answer 1

SQL fiddle似乎出现了问题，所以在此期间您需要帮助我来帮助您。

假设以下语句可以正常工作：

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)

那么你可以加入其中作为。

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)

- G B

Answer 2

我相信以下的方法可以解决问题，我的专业知识是关于Microsoft SQL Server，而不是MySQL。如果MySQL缺少其中任何一个，请告诉我，我会删除这个答案。

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+Fromm
    ELSE Fromm+Too
    END as orderedValues, MIN(compared) as minComp
FROM @Table1
GROUP BY    CASE WHEN Too < Fromm THEN Too+Fromm
ELSE Fromm+Too
END
) ordered ON 
ordered.minComp = t.Compared 
AND ordered.orderedValues = 
        CASE 
            WHEN Too < Fromm 
                THEN Too+Fromm
            ELSE 
                Fromm+Too
        END

我使用了int而不是时间值，但它的作用是相同的。这很不规范，但它给了我期望中的结果。

基本思路是使用派生查询，其中您获取要获取唯一值的两个列，并使用case语句将它们组合成标准格式。在这种情况下，先前按字母顺序连接后面的值按字母顺序连接。使用该值获取我们正在查找的最小值，再加入原始表以再次分离出值以及该表中的其他任何内容。它假定我们正在聚合的值将是唯一的，因此在这种情况下，如果有(1，'John'，'Mary'，2)和(2，'Mary'，'John'，2)，它会有点崩溃并返回该夫妇的2条记录。

- David Jacobsen

Answer 3

这个答案最初是受到获取每组分组SQL结果中最大值的记录的启发，但后来我进一步研究并找到了正确的解决方案。

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');

已在MySQL 5.6.19上进行测试

SELECT * 
FROM 
    (
        SELECT * 
        FROM T 
        ORDER BY LEAST(`to`,`from`), GREATEST(`to`,`from`), somedate DESC
    ) X
GROUP BY LEAST(`to`,`from`), GREATEST(`to`,`from`)

结果集

id from    to      somedate
3  Number2 Number1 2015-01-03
4  Number3 Number1 2015-01-04
5  Number3 Number2 2015-01-05

但是，这依赖于MySQL的一些不可靠行为，这在未来版本中将会改变。MySQL 5.7 拒绝此查询，因为SELECT子句中的列与GROUP BY列没有函数依赖关系。如果它被配置为接受它（ONLY_FULL_GROUP_BY已禁用），它就像以前的版本一样工作，但仍然不保证：“服务器可以从每个组中选择任何值，因此除非它们相同，否则所选的值是不确定的。”

因此，正确的答案似乎是这个：

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)

结果集与上面相同，但在这种情况下，它保证保持不变，而以前你可能会根据表上的索引轻松获得不同的日期和行Number2，Number1的ID。

只要原始数据中有两行具有完全相同的somedate、to和from，它就会按预期工作。

让我们再添加一行：

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

上面的查询将返回两行数据，分别对应于2015-01-03。

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

为了解决这个问题，我们需要一种方法来选择组中的唯一一行。在这个例子中，我们可以使用唯一的ID来打破平局。如果在组中有多行具有相同的最大日期，我们将选择具有最大ID的行。

内部子查询称为“Groups”，只是返回所有的组，就像问题中的原始查询一样。然后我们在结果集中添加一个列“id”，并选择属于同一组且具有最高“somedate”和最高“id”的“id”，这是通过“ORDER BY”和“LIMIT”完成的。这个子查询被称为“GroupsWithIDs”。一旦我们拥有了所有的组和每个组的正确行的“id”，我们就将其与原始表连接起来，以获取找到的“id”的其余列。

最终查询

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

最终结果集

id from    to      somedate
4  Number3 Number1 2015-01-04
5  Number3 Number2 2015-01-05
6  Number1 Number2 2015-01-03

- Vladimir Baranov