MySQL副問い合わせについて

Question

副問い合わせについて

create table asobi_a(name varchar(50),flag int,jotai varchar(2),sex int,age int);
insert into asobi_a values("a",0,"00","0",21);
insert into asobi_a values("b",0,"00","1",18);
insert into asobi_a values("c",0,"00","1",15);
insert into asobi_a values("d",0,"00","0",21);
insert into asobi_a values("e",0,"00","1",42);
insert into asobi_a values("f",0,"00","0",34);
insert into asobi_a values("g",0,"00","0",76);
insert into asobi_a values("h",0,"00","0",18);
insert into asobi_a values("i",0,"00","1",20);
insert into asobi_a values("j",0,"00","1",35);
insert into asobi_a values("k",0,"00","1",16);
insert into asobi_a values("l",0,"00","0",26);
insert into asobi_a values("m",0,"00","0",53);
insert into asobi_a values("n",0,"00","0",21);
insert into asobi_a values("o",0,"00","1",32);
+------+------+-------+------+------+
| name | flag | jotai | sex  | age  |
+------+------+-------+------+------+
| a    |    0 | 00    |    0 |   21 |
| b    |    0 | 00    |    1 |   18 |
| c    |    0 | 00    |    1 |   15 |
| d    |    0 | 00    |    0 |   21 |
| e    |    0 | 00    |    1 |   42 |
| f    |    0 | 00    |    0 |   34 |
| g    |    0 | 00    |    0 |   76 |
| h    |    0 | 00    |    0 |   18 |
| i    |    0 | 00    |    1 |   20 |
| j    |    0 | 00    |    1 |   35 |
| k    |    0 | 00    |    1 |   16 |
| l    |    0 | 00    |    0 |   26 |
| m    |    0 | 00    |    0 |   53 |
| n    |    0 | 00    |    0 |   21 |
| o    |    0 | 00    |    1 |   32 |
+------+------+-------+------+------+
create table asobi_b(bl char(2),name varchar(50));
insert into asobi_b values("a","a");
insert into asobi_b values("o","b");
insert into asobi_b values("ab","c");
insert into asobi_b values("a","d");
insert into asobi_b values("b","e");
insert into asobi_b values("b","f");
insert into asobi_b values("b","g");
insert into asobi_b values("o","h");
insert into asobi_b values("a","i");
insert into asobi_b values("a","j");
insert into asobi_b values("a","k");
insert into asobi_b values("o","l");
insert into asobi_b values("a","m");
insert into asobi_b values("b","n");
insert into asobi_b values("ab","o");
+------+------+
| bl   | name |
+------+------+
| a    | a    |
| o    | b    |
| ab   | c    |
| a    | d    |
| b    | e    |
| b    | f    |
| b    | g    |
| o    | h    |
| a    | i    |
| a    | j    |
| a    | k    |
| o    | l    |
| a    | m    |
| b    | n    |
| ab   | o    |
+------+------+

select
	sex,count(*)
from
	asobi_a
where 
	age IN
(
select
    sex,avg(AGE)
from
    asobi_a,asobi_b
where
    ((asobi_b.bl = "a" and asobi_a.sex = "0") 
    or (asobi_b.bl = "o" and asobi_a.sex = "1"))
    and asobi_b.bl in("a","o")
    and (asobi_a.name  =  asobi_b.name
    and asobi_a.FLAG = 0 and asobi_a.JOTAI = "00")
group by 
    sex
)
group by
	sex;

副問い合わせの中は大まかに言えば血液型がaかつ、男性か、血液型がoかつ、女性の人の平均年齢を性別ごとに求めています。

そして、副問い合わせが
(asobi_b.bl = "a" and asobi_a.sex = "0")の場合asobi_a.sex = "0"の平均年齢と一致する人を、(asobi_b.bl = "o" and asobi_a.sex = "1")の場合asobi_a.sex = "0"の平均年齢と一致する人を
出力したいのですが、今のSQL文だと、where句の中に、age in や sex = "0" などとりあえず入れてみたものの
ERROR 1241 (21000): Operand should contain 1 column(s)
と出てしまいます。

エラーが出る原因も何をすればエラーが出ないのかも分かりません。ぜひわかれば教えて頂きたいです。

バージョンは8.0です。

Comments

[Orlofsky]

SQLは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。
CREATE TABLE文、データを何件かINSERT文で、現行のSELECT結果と希望するSELECT結果も追記してください。

[hope_mucci]

MySQLのバージョンを書きましょう。編集で本文を修正してください。
特に8.0以降かどうかで回答が大幅に変わる可能性があります。

[hope_mucci]

Orlofskyさんがおっしゃるようにテーブル定義、サンプルデータがないと回答のしようがありません。回答者側でテストができないし、以下のようなことが判然としないので。
・asobi_aテーブルとasobi_bテーブルの役割がどういうものかが質問文からわかりません。
・両テーブルの結合条件がコードから正しいのかも分かりません。
・sexとageはどちらのテーブルに所属しているのかもわかりません。

[退会済みユーザー]

返信が大変遅くなり申し訳ございませんでした。
ただいま編集させていただきました。
また足りていないところがございましたら教えていただけると嬉しいです。

[hope_mucci]

まだ真に実施したいことが見えてきません。
男性側だけに問題を絞ると、以下のどちらかを集計したいと見えますがどちらでしょうか。
・A型の男性の集団に対し、「その集団の平均年齢」と一致する人物の人数を計算する
・A型の男性の集団に対し、「男性全体の平均年齢」と一致する人物の人数を計算する

[退会済みユーザー]

2番目の男性全体の方です！
説明が下手くそで申し訳ないです。

Answer 1

質問のクエリがエラーになる原因

where age IN ( サブクエリ )

のサブクエリの中身が2列以上の結果を返すからです。
IN句の中身をサブクエリで表現するには、何行あっても良いですが1列で返さないといけません。

問題解決

上記のクエリは一度ご破算にして、一からどうすればいいか考えます。

手順としては、

• 男女別の平均年齢を求める
• 特定の血液型の人間に対し、同性の平均年齢と一致するかどうかを調べる

となるでしょう。これを順番に解決していきます。

※MySQL8.0から導入された共通テーブル式（with句）の知識が必須です。

まず、男女別の平均年齢を求めます。これは簡単にできると思います。sexでgroup byしてavgを取るだけです。

select
    ta.sex
    ,avg(ta.age) as avg_age
from
    asobi_a ta, asobi_b tb
where
    ta.name = tb.name
    and ta.flag = 0
    and ta.jotai = "00"
group by
    ta.sex

sex	avg_age
0	33.7500
1	25.4286

次に、A型男性に対し、上記平均年齢と一致する人数が何人いるか数えます。
whereでA型男性であるかと年齢が男性平均年齢と一致するかを指定します。
血液型と年齢は別テーブルなのでnameで結合します。

select
    'sex=0,bl=A' as caption
    ,count(*)
from asobi_a as ta, asobi_b as tb
where
    ta.name = tb.name
    and ta.sex = 0 and tb.bl = 'a'  -- 男性かつA型
    and ta.age = ( /* 男性平均年齢 */ )

コメントの男性平均年齢の部分をどうするかというと、with句を用いて先に平均年齢のクエリを作成し、後続のSQLでコメントの部分にサブクエリとして投入します。

with q1 as (  -- q1という別名で性別平均年齢の表を作成
    select
        ta.sex
        ,avg(ta.age) as avg_age
    from
        asobi_a as ta
    where
        ta.flag = 0
        and ta.jotai = "00"
    group by
        ta.sex
)
select
    'sex=0,bl=A' as caption
    ,count(*)
from asobi_a as ta, asobi_b as tb
where
    ta.name = tb.name
    and ta.sex = 0 and tb.bl = 'a'  -- 男性かつA型
    and ta.age = 
        (select tq1.avg_age from q1 as tq1 where tq1.sex=0)  -- ageが男性の平均年齢と一致すれば抽出

この場合のサブクエリが返す結果は、スカラ値（1行1列）でないとエラーになります。
残念ながらテストデータでは1件も抽出されませんが、このように複雑でないクエリの組み合わせで解を得ることができます。

もう一つの「女性O型」を抽出する場合、同じようにselect文を作り、union allするのが良いかと思います。この方法なら他の複雑な条件も含めて抽出する必要がある場合でも容易に対応できるかと思います。

(withの部分は同じなので略)
select
    'sex=0,bl=A' as caption
    ,count(*)
from asobi_a as ta, asobi_b as tb
where
    ta.name = tb.name
    and ta.sex = 0 and tb.bl = 'a'  -- 男性かつA型
    and ta.age = 
        (select tq1.avg_age from q1 as tq1 where tq1.sex=0)  -- ageが男性の平均年齢と一致すれば抽出
union all
select
    'sex=1,bl=O' as caption
    ,count(*)
from asobi_a as ta, asobi_b as tb
where
    ta.name = tb.name
    and ta.sex = 1 and tb.bl = 'o'  -- 女性かつO型
    and ta.age = 
        (select tq1.avg_age from q1 as tq1 where tq1.sex=1)  -- ageが女性の平均年齢と一致すれば抽出

Comments

[退会済みユーザー]

わざわざご丁寧にありがとうございます!!
こちらを参考に作成したら完成しました!!本当にありがとうございます!!

Answer 2

項目名はすべて小文字なのですね。
また、男性は男性の平均年齢なのでasobi_a.sex = '1'ですね。
質問にあるデータだと一致するレコードはないですが、以下のようになると思います。
（女性の血液型をB型にすれば1件ヒットします。）

select
    sex,count(*)
from
    asobi_a,asobi_b
where
    (asobi_b.bl = 'a' and asobi_a.sex = '0'
     and asobi_a.age = (select round(avg(age)) from asobi_a where asobi_a.sex = '0')
     or
     asobi_b.bl = 'o' and asobi_a.sex = '1'
     and asobi_a.age = (select round(avg(age)) from asobi_a where asobi_a.sex = '1'))
    and asobi_a.name  =  asobi_b.name
    and asobi_a.flag = 0 and asobi_a.jotai = '00'
group by 
    sex;

Comments

[退会済みユーザー]

ご回答ありがとうございます。
教えていただいた通りやってみたのですがやはりエラーが出てしまいました。
ただいま編集しましたのでまた何かわかることがありましたら教えていただけると嬉しいです。
また、男性は男性の、女性は女性の平均年齢と一致する人数を知りたいです。
説明がへたくそで申し訳ないです。

[etsuhisa]

SQLを質問のテーブルに合わせて書き換えました。

[退会済みユーザー]

何度もありがとうございます!!
とても参考になりました!!

Answer 3

具体的な情報がないのでなんとも言えませんがSQL文をみるかぎり
where句が条件文になっていないので、文法的におかしいと思います

Comments

[退会済みユーザー]

ご回答ありがとうございます。
where句には何を入れてもエラーになってしまったので何も入れていないです。