mysqlで行を列に変換する方法

Mysql の行から列へのメソッド: 1. 「SUM(CASE テーブル名 WHEN フィールド名 THEN スコア ELSE 0 END) をフィールド名として」演算変換を使用します。 2. 「SUM(IF(テーブル名 = フィールド)」を使用します。 name,score,0)) をフィールド名として」という操作の変換。

このチュートリアルの動作環境: Windows10 システム、mysql8.0.22 バージョン、Dell G3 コンピューター。

mysql の行を列に変換する方法

行から列へ

つまり、同じ列の下にある複数の行の異なる内容が複数のフィールドと対応する内容として使用されます。が出力されます。

テーブル ステートメントの作成

DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

データの挿入

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

データ テーブルの内容 (つまり、変換前の結果) をクエリします

SELECT * FROM tb_score

見てみましょう最初に変換を見てみましょう。 最終結果:

#ここでの行変換は、元の件名フィールドの複数の行を異なる列として選択することであることがわかります。結果セットを取得し、ユーザー ID に基づいてグループ化し、対応するスコアを表示します。

1. 使用例...いつ...その後、行を列に変換します。

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

2. IF() を使用して行を列に変換します。列:

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

注:

(1) SUM() は、subject = "言語" のレコードが対応するため、GROUP BY を使用してユーザー ID に従ってグループ化できるようにするためです。各ユーザー ID に対するスコアは 1 つのレコードのみであるため、SUM() の値は対応するレコードのスコア値と等しくなります。

userid='001'、subject='中文' のレコードが 2 つある場合、このときの SUM() の値はこれら 2 つのレコードの合計になります。 value は 2 つのレコードの中で最大の値になります。ただし、通常の状況では、ユーザーは主題に対応するスコアを 1 つだけ持つため、SUM()、MAX()、MIN()、AVG() などの集計関数を使用して行変換の効果を実現できます。

(2)条件としての IF(`subject`='中文',score,0)。つまり、スコアが次の場合、SUM()、MAX()、MIN( )、AVG() 演算です。値がない場合は、デフォルトの 0 になります。

3. SUM(IF()) を使用して列を生成し、WITH ROLLUP を使用して集計行を生成し、IFNULL を使用して集計行のタイトルを Total

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

Run として表示します。 results:

##4. SUM(IF()) を使用して列を生成し、UNION を使用して集計行を生成し、IFNULL を使用して集計行のタイトルを Total## として表示します。 #

SELECT userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid
UNION
SELECT 'TOTAL',SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) FROM tb_score

実行結果:

5. SUM(IF()) を使用して列を生成し、サブクエリを使用せずに結果を直接生成します

SELECT IFNULL(userid,'TOTAL') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid WITH ROLLUP;

実行結果:

6. 動的、カラムの不確実性状況に適しています

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

実行結果:

7. 差し込みフィールドの表示: group_concat()

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成绩 FROM tb_score
GROUP BY userid

を使用します。実行結果:

#group_concat()、マニュアル説明: この関数は、グループの接続からの NULL 以外の値を含む文字列結果を返します。

はかなり抽象的で理解しにくいです。簡単に理解すると、実際は次のようになります。 group_concat() は、同じグループに属する行を計算し、同じグループに属する列を表示します。どの列を返すかは、関数のパラメーター (フィールド名) によって決まります。グループ化には、group by で指定された列に従ってグループ化するという基準が必要です。

結論: group_concat() 関数は、同じグループに属する複数の行を 1 つの列に変換できます。

推奨学習:

mysql ビデオ チュートリアル

以上がmysqlで行を列に変換する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。