回答編集履歴

3 追記

sazi

sazi score 12185

2018/06/10 15:37  投稿

条件をSQLを構成する際に考慮していく順番に並べ替えしました。
先ずは、取得するデータの集合を考え、それらを絞り込んだり結合したり加工するという風に考えると、
混乱せずにすむのではないかと思います。
> 3.リンゴ、バナナ、オレンジ、パイナップルの全てが登録されているuser_idを取得
> 4.―1リンゴ、バナナ、オレンジの3つが登録されているuser_idを取得する
> 4.―2(3.)の条件を満たすデータが無い場合、(4.―1)の条件を満たすuser_idを取得する
> 2.取得するuser_idは重複しない
> 1.取得するデータはfavoテーブルのuser_idを2つ取得する
> 5.並び順は最も条件を満たしている順である事
3と4の集合の完全外部結合より条件を加えます。
(MySQLでは完全外部結合は使えないので、代替え方法を使用)
```SQL
select base.user_id
from (
   select distinct user_id from favo
) base
left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ','パイナップル')
   group by user_id
   having count(distinct value)=4
 ) as count4
 on base.user_id=count4.user_id
 left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ')
   group by user_id
   having count(distinct value)=3
 ) as count3
 on base.user_id=count3.user_id
where coalesce(count4.user_id,count3.user_id) is not null
order by coalesce(count4.value_count,count3.value_count) desc
      , base.user_id
limit 2
```
追記
--
条件の要素を昇順で指定する前提であれば、group_concat()を使用して簡潔に記述することもできます。
最初のSQLとは基にする集合をどうするかという点が異なります。  
```SQL
select *
from (
   select user_id, count(distinct value) as value_count
        , group_concat(distinct value order by value) as value_list
   from favo
   group by user_id
) agg
where value_list in ('オレンジ,パイナップル,バナナ,リンゴ','オレンジ,バナナ,リンゴ')
order by value_count desc, user_id
limit 2
```
2 追記

sazi

sazi score 12185

2018/06/10 15:35  投稿

条件をSQLを構成する際の順番に並べ替えしました。
条件をSQLを構成する際に考慮していく順番に並べ替えしました。
先ずは、取得するデータの集合を考え、それらを絞り込んだり結合したり加工するという風に考えると、
混乱せずにすむのではないかと思います。
> 3.リンゴ、バナナ、オレンジ、パイナップルの全てが登録されているuser_idを取得
> 4.―1リンゴ、バナナ、オレンジの3つが登録されているuser_idを取得する
> 4.―2(3.)の条件を満たすデータが無い場合、(4.―1)の条件を満たすuser_idを取得する
> 2.取得するuser_idは重複しない
> 1.取得するデータはfavoテーブルのuser_idを2つ取得する
> 5.並び順は最も条件を満たしている順である事
3と4の集合の完全外部結合より条件を加えます。
(MySQLでは完全外部結合は使えないので、代替え方法を使用)
```SQL
select base.user_id
from (
   select distinct user_id from favo
) base
left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ','パイナップル')
   group by user_id
   having count(distinct value)=4
 ) as count4
 on base.user_id=count4.user_id
 left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ')
   group by user_id
   having count(distinct value)=3
 ) as count3
 on base.user_id=count3.user_id
where coalesce(count4.user_id,count3.user_id) is not null
order by coalesce(count4.value_count,count3.value_count) desc
      , base.user_id
limit 2
```
追記
--
条件の要素を昇順で指定する前提であれば、group_concat()を使用して簡潔に記述することもできます。
```SQL
select *
from (
   select user_id, count(distinct value) as value_count
        , group_concat(distinct value order by value) as value_list
   from favo
   group by user_id
) agg
where value_list in ('オレンジ,パイナップル,バナナ,リンゴ','オレンジ,バナナ,リンゴ')
order by value_count desc, user_id
limit 2
```
1 追記

sazi

sazi score 12185

2018/06/10 13:00  投稿

条件をSQLを構成する際の順番に並べ替えしました。
> 3.リンゴ、バナナ、オレンジ、パイナップルの全てが登録されているuser_idを取得
> 4.―1リンゴ、バナナ、オレンジの3つが登録されているuser_idを取得する
> 4.―2(3.)の条件を満たすデータが無い場合、(4.―1)の条件を満たすuser_idを取得する
> 2.取得するuser_idは重複しない
> 1.取得するデータはfavoテーブルのuser_idを2つ取得する
> 5.並び順は最も条件を満たしている順である事
3と4の集合の完全外部結合より条件を加えます。
(MySQLでは完全外部結合は使えないので、代替え方法を使用)
```SQL
select base.user_id
from (
   select distinct user_id from favo
) base
left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ','パイナップル')
   group by user_id
   having count(distinct value)=4
 ) as count4
 on base.user_id=count4.user_id
 left join (
   select user_id, count(distinct value) as value_count
   from favo
   where value in('リンゴ','バナナ','オレンジ')
   group by user_id
   having count(distinct value)=3
 ) as count3
 on base.user_id=count3.user_id
where coalesce(count4.user_id,count3.user_id) is not null
order by coalesce(count4.value_count,count3.value_count) desc
      , base.user_id
limit 2
```  
追記  
--  
条件の要素を昇順で指定する前提であれば、group_concat()を使用して簡潔に記述することもできます。  
```SQL  
select *  
from (  
   select user_id, count(distinct value) as value_count  
        , group_concat(distinct value order by value) as value_list  
   from favo  
   group by user_id  
) agg  
where value_list in ('オレンジ,パイナップル,バナナ,リンゴ','オレンジ,バナナ,リンゴ')  
order by value_count desc, user_id  
limit 2  
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る