MySQLにクロス集計クエリ2（動的クロス集計）を実装する
Implement Crosstab Query 2 Mysql
クロステーブルは、静的クロステーブルと動的クロステーブルに分けられます。静的クロス集計の列は固定されているため、動的クロス集計の列を比較的簡単に実装するには、動的に生成する必要があります。
1.静的クロス集計の実装
前の記事を参照してください：MySQLでのクロスタブクエリ1（静的クロスタブ）の実装
https://blog.csdn.net/weixin_44377973/article/details/103099573。
第二に、動的クロス集計の実現
学生テーブル、コーステーブル、スコアテーブルの3つのテーブルを作成します
create table student( s_id char (10) comment 'Student ID', s_name char (20) not null default '' comment 'Name', primary key(s_id) ) create table course( c_id char(5), c_name char (20) not null default '' comment 'Course name', primary key(c_id) ) create table score( s_id char(10), c_id char(5), score int not null default 0, primary key(s_id,c_id) ) 上記の3つのテーブルのデータを追加します。
Insert Into student(s_id,s_name) Values ('2018001001', 'Zhang Xiaoming'), ('2018001002', 'Li Xiaosi'), ('2018001003', 'Zhao Xiaoer'), ('2018001004', 'Wang Xiaowu'), ('2018001005', 'Liu Changjiang'), ('2018001006', 'Zhou Guangming') Insert Into course(c_id,c_name) Values ('C0001', 'Database'), ('C0002', 'Data Structure'), ('C0003', 'Advanced Mathematics'), ('C0004', 'Marketing'), ('C0005', 'E-commerce'), ('C0006', 'Internet Marketing'), ('C0007', 'Logistics Management') Insert Into score(s_id,c_id,score) Values('2018001001','C0001',88),('2018001002', 'C0001',76),('2018001003','C0001',62), ('2018001004','C0001',74),('2018001005','C0001',61),('2018001006','C0001',58), ('2018001001','C0002',66),('2018001002','C0002',54),('2018001003','C0002',72), ('2018001004','C0002',69),('2018001005','C0002',65),('2018001006','C0002',70), ('2018001001','C0003',83),('2018001002','C0003',68),('2018001003','C0003',85), ('2018001004','C0003',75),('2018001005','C0003',63),('2018001006','C0003',42), ('2018001001','C0004',28),('2018001002','C0004',99),('2018001003','C0004',92), ('2018001004','C0004',91),('2018001005','C0004',74),('2018001006','C0004',88), ('2018001001','C0005',77),('2018001002','C0005',66),('2018001003','C0005',70), ('2018001004','C0005',80),('2018001005','C0005',69),('2018001006','C0005',82), ('2018001001','C0006',64),('2018001002','C0006',62),('2018001003','C0006',66), ('2018001004','C0006',80),('2018001005','C0006',71),('2018001006','C0006',60) ステップ1：静的クロス集計に類似したステートメントを動的に取得する
select IFNULL (s_name, 'total score') as 'name', sum (if (c_name = 'database', score, 0)) as 'database', sum (if (c_name = 'data structure', score, 0)) as 'data structure', sum (if (c_name = 'Advanced Mathematics', score, 0)) as 'Advanced Mathematics', sum (if (c_name = 'Marketing', score, 0)) as 'Marketing', sum (if (c_name = 'e-commerce', score, 0)) as 'e-commerce', sum (if (c_name = 'network marketing', score, 0)) as 'network marketing', sum (score) as 'total score' from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id group by s_name with rollup 上記のステートメントを動的に生成する場合は、SQLステートメントのスティッチングを使用する必要があります。コマンドは次のとおりです。
select group_concat(distinct concat('sum(if(c.c_name='',c_name),'',sc.score,0)) as '',c.c_name,''') from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id 上記のコマンドのクエリ結果は次のとおりです。
sum (if (c_name = 'marketing', score, 0)) as' marketing ', sum (if (c_name =' database ', score, 0)) as' database', sum (if (c_name = 'data Structure ', score, 0)) as' Data Structure', sum (if (c_name = 'e-commerce', score, 0)) as' E-commerce ', sum (if (c_name =' Internet Marketing ', score, 0 )) as 'Internet Marketing', sum (if (c_name = 'Advanced Mathematics', score, 0)) as 'Advanced Mathematics' このようにして、特定のコース数やコース名を考慮せずにコードを修正できます。
2番目のステップ：完全なSQLステートメントをつなぎ合わせる
set @sql=null select group_concat(distinct concat('sum(if(c_name='',c_name),'',score,0)) as '',c_name,''') into @sql from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id set @ sql = concat ('select IFNULL (s_name, ' total score ') as ' name ',', @ sql, ', sum (score) as ' total score ' from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id group by s_name with rollup') prepare stmt from @sql execute stmt deallocate prepare stmt group_concatの使用法については、私の個人ブログ「MySQLでのgroup_concat関数の使用法の要約」を参照してください。クエリ結果は次のとおりです。
mysql> execute stmt +-----------+--------------+-----------+--------------+--------------+--------------+--------------+--------+ | Name | Marketing | Database | Data Structure | E-commerce | Internet Marketing | +-----------+--------------+-----------+--------------+--------------+--------------+--------------+--------+ Liu Changjiang | 74 | 61 | 65 | 69 | 71 | 63 | 403 | Zhou Guangming | 88 | 58 | 70 | 82 | 60 | 42 | 400 | Zhang Xiaoming | 28 | 88 | 66 | 77 | 64 | 83 | 406 | Li Xiaosi | 99 | 76 | 54 | 66 | 62 | 68 | 425 | Wang Xiaowu | 91 | 74 | 69 | 80 | 80 | 75 | 469 | Zhao Xiaoer | 92 | 62 | 72 | 70 | 66 | 85 | 447 | Total score | 472 | 419 | 396 | 444 | 403 | 416 | 2550 | +-----------+--------------+-----------+--------------+--------------+--------------+--------------+--------+ 7 rows in set (0.01 sec) プリペアドステートメントの構文の説明：
SQL文字列からstatement_nameを準備します
excutestatement_name-プリペアドステートメントを実行します
割り当て解除| drop preparestatement_name-定義を削除します
ステップ3：ストアドプロシージャを作成する
MySQLで直接操作する場合は、2番目のステップに進むことができますが、JAVAやPHPなどのプロジェクトで使用する場合は問題が発生するため、ストアドプロシージャを作成する必要があります。ストアドプロシージャを作成するときは、2番目のステップで生成されたコードをストアドプロシージャの開始と終了の間に直接配置するだけで済みます。コードは次のとおりです。
drop procedure if exists sp_query_crosstable delimiter && create procedure sp_query_crosstable() reads SQL data begin set @sql=null select group_concat(distinct concat('sum(if(c_name='',c_name),'',score,0)) as '',c_name,''') into @sql from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id set @ sql = concat ('select IFNULL (s_name, ' total score ') as ' name ',', @ sql, ', sum (score) as ' total score ' from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id group by s_name with rollup') prepare stmt from @sql execute stmt deallocate prepare stmt end && delimiter ストアドプロシージャを呼び出します。
call sp_query_crosstable