質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,080

score 114

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+1

こんな漢字で、

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/11 10:14

    ありがとうございます。
    to_userに含まれるAさんと
    from_userに含まれるAさんのレコードがグループ化できていないようです。

    Aさん Bさん
    Bさん Aさん

    キャンセル

+1

実行してませんがこんな感じでいけると思います。
datがユニークでなければ読み替えてください。

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
)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/11 00:43

    ありがとうございます。

    Unknown column 'w.dat' in 'field list'

    となります。
    カラム名はdatで間違いありません。

    キャンセル

  • 2019/01/11 01:03

    -- Aさんの相手の重複を削除して最大のdatを取得
    SELECT MAX(w.dat) AS max_dat FROM (

    -- Aさんの相手の重複を削除して最大のdatを取得
    SELECT MAX(w2.dat) AS max_dat FROM (

    とすることで回避できました!

    キャンセル

  • 2019/01/11 01:07

    ちなみに
    別テーブル
    name age sex
    Bさん 20才 男
    Cさん 30才 女

    をnotA =nameでJOINする方法はありますでしょうか?

    キャンセル

checkベストアンサー

0

  • 元データ
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さん');
  • 抽出
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

追加

create table user(name varchar(10),age int, sex set('男','女'));
insert into user values
('Bさん',20,'男'),
('Cさん',30,'女');
  • ユーザー情報つき
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に変えてください

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/11 09:50

    いつもありがとうございます。
    同じ結果を得られました。

    ちなみにこれに
    別テーブル
    name age sex
    Bさん 20才 男
    Cさん 30才 女

    をnot_a =nameでJOINする方法はありますでしょうか?

    キャンセル

  • 2019/01/11 10:01

    追記しました。
    こういうのは単純にサブクエリーにしてしまうのが簡単です

    キャンセル

  • 2019/01/11 10:08

    理想の結果を得られました。

    なるほど、最初のものをサブクエリかして、それにJOINするんですね
    勉強になります。

    またよろしくお願いします。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る