mysqlで指定した条件でデータを抽出したい

Question

mysqlにて下記のようなテーブルから

**tableA**
|no|dat|to_user|from_user|
|:--|:--:|--:|--:|
|10|2018-12-31 12:10:00|Aさん|Bさん|
|9|2018-12-30 12:10:00|Bさん|Aさん|
|8|2018-12-30 11:10:00|Aさん|Bさん|
|7|2018-12-21 12:10:00|Cさん|Aさん|
|6|2018-12-11 12:10:00|Dさん|Aさん|
|5|2018-12-01 12:10:00|Cさん|Bさん|
|4|2018-11-20 12:10:00|Aさん|Bさん|
|3|2018-11-10 12:10:00|Aさん|Cさん|
|2|2018-11-01 12:10:00|Aさん|Dさん|
|1|2018-10-21 12:10:00|Cさん|Aさん|

抽出条件として
①Aさんがto_userまたはfrom_userに含まれる
②相手（Aさんでない方のカラム）の重複は除く
③それぞれdatの最新のもの

下記のようなデータを抽出したい
|no|dat|to_user|from_user|
|:--|:--:|--:|--:|
|10|2018-12-31 12:10:00|Aさん|Bさん|
|7|2018-12-21 12:10:00|Cさん|Aさん|
|6|2018-12-11 12:10:00|Dさん|Aさん|

**試したquery**
```mysql
SELECT v1.* FROM
(SELECT *,to_user as myno FROM tableA WHERE no IN( SELECT MAX(no) FROM tableA WHERE from_user='Aさん' GROUP BY to_user)
 UNION ALL SELECT *,from_user as myno FROM tableA WHERE no IN( SELECT MAX(no) FROM tableA WHERE to_user='Aさん' GROUP BY from_user )) AS v1
GROUP bY myno
ORDER BY v1.dat DESC
```

としたのですが、①②を満たすデータは取れるのですが③datが最新のものの抽出ができません。
「mysql group 最大値」と検索した結果にヒントはあると思うのですが
UNION で連結していることで複雑になりわからなかったので、質問しました。


mac
chrome
php7.X
mysql5.7

Accepted Answer

- 元データ
```SQL
create table tbl(no int,dat datetime,to_user varchar(20),from_user varchar(20));
insert into tbl values
(10,'2018-12-31 12:10:00','Aさん','Bさん'),
( 9,'2018-12-30 12:10:00','Bさん','Aさん'),
( 8,'2018-12-30 11:10:00','Aさん','Bさん'),
( 7,'2018-12-21 12:10:00','Cさん','Aさん'),
( 6,'2018-12-11 12:10:00','Dさん','Aさん'),
( 5,'2018-12-01 12:10:00','Cさん','Bさん'),
( 4,'2018-11-20 12:10:00','Aさん','Bさん'),
( 3,'2018-11-10 12:10:00','Aさん','Cさん'),
( 2,'2018-11-01 12:10:00','Aさん','Dさん'),
( 1,'2018-10-21 12:10:00','Cさん','Aさん');
```

- 抽出
```SQL
select no,dat,elt(field('Aさん',to_user,from_user),from_user,to_user) as not_a
from tbl as t1
having not exists(select 1 
from tbl
where 'Aさん' in(to_user,from_user)
and elt(field('Aさん',to_user,from_user),from_user,to_user)=not_a
and dat>t1.dat
)
and not_a is not null
```

# 追加
```SQL
create table user(name varchar(10),age int, sex set('男','女'));
insert into user values
('Bさん',20,'男'),
('Cさん',30,'女');
```

- ユーザー情報つき
```SQL
select * from (
select no,dat,elt(field('Aさん',to_user,from_user),from_user,to_user) as not_a
from tbl as t1
having not exists(select 1 
from tbl
where 'Aさん' in(to_user,from_user)
and elt(field('Aさん',to_user,from_user),from_user,to_user)=not_a
and dat>t1.dat
)
and not_a is not null
)
as t2
inner join user as t3 on t2.not_a=t3.name
```
※上記だとDさんがuserテーブルにないので消えてしまいます
ユーザー情報のないuserもnullで個人情報を表示したいなら
inner joinをleft joinに変えてください

Answer

実行してませんがこんな感じでいけると思います。
datがユニークでなければ読み替えてください。
``` SQL
SELECT * FROM tableA t
WHERE EXISTS (

  -- datがユニークの前提で結合
  SELECT * FROM (

    -- Aさんの相手の重複を削除して最大のdatを取得
    SELECT MAX(w.dat) AS max_dat FROM (

      -- Aさんを含むdatとAさんの相手を取得
      SELECT
        w.dat
        , CASE w.to_user
            WHEN 'Aさん' THEN w.from_user
            ELSE w.to_user
          END AS notA
      FROM tableA w
      WHERE to_user = 'Aさん' OR from_user = 'Aさん'

    ) w2
    GROUP BY w2.notA 

  ) w3
  WHERE w3.max_dat = t.dat
)

```

Answer

こんな漢字で、
```SQL
SELECT
  MAX(a2.no) AS no
  ,a2.dat
  ,a2.to_user
  ,a2.from_user
FROM
  ( SELECT
      MAX(a.dat) AS dat
      ,a.to_user
      ,a.from_user
    FROM
      tableA a
    WHERE
      a.to_user = 'Aさん'
      OR a.from_user='Aさん'
    GROUP BY
      a.to_user
      ,a.from_user
  ) a1 JOIN tableA a2 ON
      a1.dat = a2.dat
      AND a1.to_user = a2.to_user
      AND a1.from_user = a2.from_user
GROUP BY
  a2.dat
  ,a2.to_user
  ,a2.from_user
ORDER BY
  a2.dat DESC
  ,a2.to_user
  ,a2.from_user
```

no	dat	to_user	from_user
10	2018-12-31 12:10:00	Aさん	Bさん
9	2018-12-30 12:10:00	Bさん	Aさん
8	2018-12-30 11:10:00	Aさん	Bさん
7	2018-12-21 12:10:00	Cさん	Aさん
6	2018-12-11 12:10:00	Dさん	Aさん
5	2018-12-01 12:10:00	Cさん	Bさん
4	2018-11-20 12:10:00	Aさん	Bさん
3	2018-11-10 12:10:00	Aさん	Cさん
2	2018-11-01 12:10:00	Aさん	Dさん
1	2018-10-21 12:10:00	Cさん	Aさん

追加

関連した質問