mysql二维分组,mysql-SQL如何按两列分组

贝娄是一个示例表.

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 ASC订购)

(1,Number1,Number2)

(4,Number3,Number1)

(5,Number3,Number2)

好的,我发现如何使用以下查询执行此操作

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

但是,我无法获得每个唯一对的最新记录.

我尝试使用ID desc命令,但它返回唯一对的第一个找到的行.

解决方法:

SQL提琴由于某种原因无法正常工作,因此与此同时,您需要帮助我.

假设以下语句有效

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)

标签：sql,mysql

来源： https://codeday.me/bug/20191028/1953691.html