sql練習　

Topic：

今回のSQL練習テーマは、全員の最終ログイン日（二）、前回の 全員の最終ログイン日（一）に続いて、難度は少し上がります、inner join、order by、group by、集計関数max()を使って問題を解きましょう。

登録テーブル login は以下のように:

+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       2 |         2 | 2020-10-13 |
|  4 |       3 |         2 | 2020-10-13 |
+----+---------+-----------+------------+

意味を説明する：

1行目は、user_id 2 のユーザーが 2020 年 10 月 12 日にクライアント ID 1 のデバイスを使用して、 Aサイドにログインした。

4行目は、user_id 3 のユーザーが 2020 年 10 月 13 日にクライアント ID 2 のデバイスを使用して、 Aサイドにログインした。

ユーザーテーブル（user）は以下のように:

+----+----------+
| id | name     |
+----+----------+
|  1 | tm       |
|  2 | fh       |
|  3 | wangchao |
+----+----------+

サイトに登録用設備表（client）は以下のように:

+----+--------+
| id | name   |
+----+--------+
|  1 | pc     |
|  2 | ios    |
|  3 | anroid |
|  4 | h5     |
+----+--------+

問題：

各ユーザーが最終ログイン日とそのユーザー名、ユーザーが使用したデバイス名をクエリする。クエリ結果はユーザー名の昇順で並べ替えられます。

解答：

まず、ユーザーのグループ化にして、各ユーザーのログインの最新の日付を見つけます。

select user_id, max(date) as day from login group by user_id ;

次に、すべてのユーザーの名前、すべてのログインデバイス、およびすべてのログイン日を調べます。

select l.user_id as id, u.name as u_n, c.name as c_n, l.date
from login as l
 inner join user as u
 inner join client as c
on l.user_id = u.id 
and l.client_id = c.id;

最後に、ユーザー ID と最新のログイン日に従って、唯一のデータセットを探し出して、名前の昇順で並び替えます。

select user_detail.u_n, user_detail.c_n, login_date.day
from 
(
　select user_id, max(date) as day
　from login 
　group by user_id
　) login_date
inner join 
(
　　select l.user_id as id, u.name as u_n, c.name as c_n, l.date
　　from login as l
　　　inner join user as u
　　　inner join client as c
　　on l.user_id = u.id 
　　and l.client_id = c.id
) user_detail
on login_date.user_id = user_detail.id
and login_date.day = user_detail.date
order by user_detail.u_n asc
;

DDLとDMLは以下に添付するので、好きな方はご利用ください。

drop table if exists login;
drop table if exists user;
drop table if exists client;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `user` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `client` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,2,2,'2020-10-13'),
(4,3,2,'2020-10-13');

INSERT INTO user VALUES
(1,'tm'),
(2,'fh'),
(3,'wangchao');

INSERT INTO client VALUES
(1,'pc'),
(2,'ios'),
(3,'anroid'),
(4,'h5');

SQL　全員の最終ログイン日（一）

SQL練習 ‐ 全員の最終ログイン日（二）inner join, group by, order by

Topic：

コメント