从表中选择不同的有序对，按事件的最近日期分组 [英] Select distinct ordered pairs from table join grouped by event's most recent date

问题描述

继后从表连接中选择不同的有序对

如何选择按有序对分组的最近John的连接行，无论顺序如何（例如John - > Jane或Jane - > John）？ b
$ b

第一张表：

  table_a 
 + ------- ---- + -------------- + ------------------- + 
 | id |名称| created_at | 
 + ----------- + -------------- + ------------------ -  + 
 | 1 |约翰| 2016-08-26 15：40：21 | 
 + ----------- + -------------- + ------------------ -  + 
 | 2 | Jane | 2016-08-26 15：37：21 | 
 + ----------- + -------------- + ------------------ + 
 | 3 | Jane | 2016-08-26 15：38：21 | 
 + ----------- + -------------- + ------------------ -  + 
 | 4 |塔拉| 2016-08-26 15：39：21 | 
 + ----------- + -------------- + ------------------ -  + 
  

第二张表：

  table_b 
 + ----------- + ------------------- + ---- --------- + ------------- + 
 | id | id_table_a |名称|消息| 
 + ----------- + ------------------- + ------------- + ------------- + 
 | 1 | 1 |简|测试1 | 
 + ----------- + ------------------- + ------------- + ------------- + 
 | 2 | 2 |约翰|测试2 | 
 + ----------- + ------------------- + ------------- + ------------- + 
 | 3 | 3 | Sammy |测试3 | 
 + ----------- + ------------------- + ------------- + ------------- + 
 | 4 | 4 |约翰|测试4 | 
 + ----------- + ------------------- + ------------- + ------------- + 
  

一个可能的结果

  + ----------- + ------------- +  - ----------- + ------------- + ------------------- + 
 | id | name_a | name_b |消息| created_at | 
 + ----------- + ------------- + ------------- + ----- -------- + ------------------- + 
 | 1 |约翰|简|测试1 | 2016-08-26 15：40：21 | 
 + ----------- + ------------- + ------------- + ----- -------- + ------------------- + 
 | 4 |塔拉|约翰|测试4 | 2016-08-26 15：39：21 | 
 + ----------- + ------------- + ------------- + ----- -------- + ------------------- + 
  

sqlfiddle

非常感谢！

解决方案

这有点麻烦。这是一个使用用户定义变量在每组结果中建立 row_number 然后按行号过滤= 1：

  select * 
 from（
 select *，
 @rn：= if （@prev_name_a = name_a和@prev_name_b = name_b，@ rn + 1，
 if（@prev_name_a：= name_a，1，
 if（@prev_name_b：= name_b，1，1）
 ）
）
 from（
至少选择（a.name，b.name）作为name_a，
最大（a.name，b.name）作为name_b，
 a.created_at，
 a.id，
 b.message 
 from table_a a 
 join table_b b on a.id = b.id_table_a 
）t cross join（select @rn：= 0，@prev_name_a：= null，@prev_name_b：= null）t1 
 order by name_a，name_b，created_at desc 
）t 
 where'John'in name_a，name_b）和rn = 1 
  

• SQL小提琴演示

Following the post Select distinct ordered pair from table join

How can I select the most recent John's joined rows grouped by ordered pair, regardless the order (e.g. John -> Jane or Jane -> John)?

First table:

table_a
+-----------+--------------+-------------------+
|    id     |     name     |     created_at    |
+-----------+--------------+-------------------+
|     1     |     John     |2016-08-26 15:40:21|
+-----------+--------------+-------------------+
|     2     |     Jane     |2016-08-26 15:37:21|
+-----------+--------------+ ------------------+
|     3     |     Jane     |2016-08-26 15:38:21|
+-----------+--------------+-------------------+
|     4     |     Tara     |2016-08-26 15:39:21|
+-----------+--------------+-------------------+   

Second Table:

table_b
+-----------+-------------------+-------------+-------------+
|    id     |     id_table_a    |    name     |   message   |
+-----------+-------------------+-------------+-------------+
|    1      |          1        |    Jane     |   Test 1    |
+-----------+-------------------+-------------+-------------+
|    2      |          2        |    John     |   Test 2    |
+-----------+-------------------+-------------+-------------+
|    3      |          3        |    Sammy    |   Test 3    |
+-----------+-------------------+-------------+-------------+
|    4      |          4        |    John     |   Test 4    |
+-----------+-------------------+-------------+-------------+

One possible result

+-----------+-------------+-------------+-------------+-------------------+
|    id     |   name_a    |   name_b    |   message   |     created_at    |
+-----------+-------------+-------------+-------------+-------------------+
|    1      |   John      |   Jane      |   Test 1    |2016-08-26 15:40:21|
+-----------+-------------+-------------+-------------+-------------------+
|    4      |   Tara      |   John      |   Test 4    |2016-08-26 15:39:21|
+-----------+-------------+-------------+-------------+-------------------+

sqlfiddle

Thanks a lot!

解决方案

This gets a bit messy. Here's an option using user-defined variables to establish a row_number within each group of results and then filtering by row number = 1:

select *
from (
  select *,
    @rn := if(@prev_name_a = name_a and @prev_name_b = name_b, @rn+1,
                if(@prev_name_a:=name_a,1,
                   if(@prev_name_b:=name_b, 1, 1)
                )
             ) rn
  from (
    select least(a.name, b.name) as name_a, 
        greatest(a.name, b.name) as name_b, 
        a.created_at,
        a.id, 
        b.message
    from table_a a 
      join table_b b on a.id = b.id_table_a
  ) t cross join (select @rn:=0, @prev_name_a:=null, @prev_name_b:=null) t1
  order by name_a, name_b, created_at desc
  ) t
where 'John' in (name_a, name_b) and rn = 1

• SQL Fiddle Demo

这篇关于从表中选择不同的有序对，按事件的最近日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！