結合したテーブルの取得結果が複数件存在した場合に1件として扱えますか。

Question

【main_table】

ID	MAIN_NOTE
1	main1
2	main2
3	main3

【sub_table】

ID	MAIN_ID	SUB_NOTE
1	1	テストテキスト１
2	1	テスト,テキスト２
3	2	テストテキスト３
4	2	テストテキスト４
5	3	テストテキスト５

上記のテーブルが存在した場合、以下のSQLを実行して取得した結果を1行で取得したいです。

【SQL】

SELECT
  mt.ID MAIN_ID,
  st.ID SUB_ID,
  st.SUB_NOTE SUB_NOTE
FROM
  main_table mt
    INNER JOIN sub_table st
    ON mt.ID = st.MAIN_ID
WHERE
  mt.ID = 1

【上記SQLを実行した結果】

MAIN_ID	SUB_ID	SUB_NOTE
1	1	テストテキスト１
1	2	テスト,テキスト２

【欲しい結果】※SUB_TABLEは常に同じ件数ではない

MAIN_ID	SUB_ID1	SUB_NOTE1	SUB_ID2	SUB_NOTE2
1	1	テストテキスト１	2	テスト,テキスト２

mysqlでは「GROUP_CONCAT」が存在し、
「GROUP_CONCAT(st.SUB_NOTE SEPARATOR ',')」のように指定すれば、
1カラムに「テストテキスト１,テスト,テキスト２」とすることはできます。
しかし、区切り文字と同じ値が入力されている場合に予期せぬ値になってしまうため、
「GROUP_CONCAT」を使用する場合はPHP等で置換処理をしなければならないと思いますが、
容易にできるのでしょうか。
SQLの「REPLACE」で置換して取得し、PHPの「str_replace」で元に戻すのが容易だと思っています。

Answer 1

MAIN_ID SUB_ID1 SUB_NOTE1 SUB_ID2 SUB_NOTE2

これはSQL的は出力の項目数が不定で、項目名も不定になるのでNG
PHP的にも出力される項目名が可変では任意のデータを抽出できないのでNGですね
どうせPHPで取得するのでしたら2行でうけて1行に集約すれば良いのでは？

  $sql="SELECT MAIN_ID,SUB_ID,SUB_NOTE FROM tbl";
  $stmt = $pdo->query($sql);
  $rows=[];
  while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $rows[$row["MAIN_ID"]][]=[$row["SUB_ID","SUB_NOTE"]];
  }

のようなとり方をするとかで対応してみてください

sample

SQLでやる簡単なサンプルを付けておきます

• 元データ

create table tbl(mid int,sid int unique,snote varchar(20));
insert into tbl values
(1,1,'a'),
(1,2,'b'),
(1,3,'c'),
(1,4,'d'),
(2,5,'e'),
(2,6,'f'),
(2,7,'g'),
(3,8,'h'),
(4,9,'i'),
(4,10,'k');

• 検索

select mid
,group_concat(case rank when 1 then sid end) as sid1
,group_concat(case rank when 1 then snote end) as snote1
,group_concat(case rank when 2 then sid end) as sid2
,group_concat(case rank when 2 then snote end) as snote2
,group_concat(case rank when 3 then sid end) as sid3
,group_concat(case rank when 3 then snote end) as snote3
,group_concat(case rank when 4 then sid end) as sid4
,group_concat(case rank when 4 then snote end) as snote4
from(
select mid,sid,snote,(select count(*)+1 from tbl as t2 where t1.mid=t2.mid and t1.sid>t2.sid) as rank
from tbl t1
) as b
group by mid

上記元データはmid=1のデータが4つあると目視できるため4列書いていますが
実際にはいくつあるかは状況によって違うでしょう
原則としてgroup_concatの部分は手書きでしかSQLは指定できないので、
これをループさせて羅列する処理をprocedureで書くわけです
興味があるようならprocedureの書き方も例示します

procedure

横方向への羅列するprocedure

drop procedure if exists proc_yoko_hyoji;
delimiter //
create procedure proc_yoko_hyoji()
begin
declare cnt int;
declare len int;
select count(*) into len from tbl group by mid order by len desc limit 1;
set @sql="select mid";
set cnt=1;
while cnt<=len DO
set @sql=concat(@sql
,",group_concat(case rank when ",cnt," then sid end) as sid",cnt
,",group_concat(case rank when ",cnt," then snote end) as snote",cnt
);
set cnt=cnt+1;
end while;
set @sql=concat(@sql
," from ( "
,"select mid,sid,snote,(select count(*)+1 from tbl as t2 where t1.mid=t2.mid and t1.sid>t2.sid) as rank "
,"from tbl as t1 "
,") as sub "
,"group by mid "
);
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

※上記を一度つくっておけばあとは、データ更新などがあっても以下を実行するだけです

call proc_yoko_hyoji;

Comments

[red13]

やはり1行で取得は難しいですか。
質問のために省略していますが、
実際はメインのテーブルに結合するテーブルが11テーブルあります。
その状態でメインテーブルに対して1件取得した結果は、約150件返ってきました。
メインテーブルが約6万件あり、単純計算だと6万*150の900万件をループすることになります。
その件数をループさせることはあまりによろしくないため、出来るだけ件数を減らしたいと思っています。

[yambejp]

逆にMAIN_IDごとに1行ずつまとめるという考え方がSQL的ではありません。
１レコード１行ずつ抽出してなにか問題があるのでしょうか？
むしろ１レコードが150列（ないし2列ずつあるなら300列？）になる方が問題では？
しかもレコードごとに要素数は不定なんですよね？
データ管理おおび運用上それを1行で返すのは現実的ではないと思います

[yambejp]

ただし・・・
どうしても必要ならprocedureなどで対応はできないことはないです。
その場合抽出されるSUB_IDやNOTEの順位付を明確にしないといけません。

[red13]

結合とは考え方が違いますが、
メインテーブルの1件に対して結合するサブテーブルをビューにまとめるのも質問内容と同じような考え方でしょうか。
サブテーブルのビュー作成後にメインテーブルと結合して1レコードとして扱う等です。

[yambejp]

実際の書き方をsampleつけておきました
参考にしてみてください

[red13]

何もかも記載していただき、ありがとうございます。

結果の取得ですが
$stmt = $conn->prepare($sql);
$stmt->execute()
で取得し、
while ($row = $stmt->fetch()) {
}
でループしています。
全て取得したところ、約68万5000件でした。
floor((memory_get_usage() / (1024 * 1024)))
で、ループ中の使用メモリを取得したところ、「10M」開始で「10M」終了でした。
ループしただけなので何とも言えませんが、消費メモリが少ないのであれば
最初に回答していただいたように複数行を1行にまとめる手段が良いのかもしれません。
もともとは「Allowed memory size of ～」の解消のためにループする回数を減らしたいための質問です。

Answer 2

自己解決ではありませんが
一旦は全ループを実施してみます。
その結果が問題ないのであれば、そのまま実装します。
問題があるようであれば、また質問させていただきます。

Answer 3

GROUP_CONCAT()やSUBSTRING_INDEX()では区切り文字が指定できますので使用されない文字が限定できるなら<例えばタブ('\t')>SQLで行えるかと思います。

但し、カラム数が不定になるのは好ましくないので、SQLではGROUP_CONCAT()で集約するに留めておき、分解するのはPHP側で行うのが良いかと思います。

Comments

[red13]

使用できない文字の制限は設けていないため、区切り文字を指定するのは難しいです。
ランダムな文字列を作成して、それを使用することも考えましたが、確実ではないので止めました。