召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2309|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:; b9 v5 o' R7 i3 S5 O  o
要求查询结果如下 :1 S, a, U$ \# s, O) c
; y" k, B% k: _4 A. p

' u/ l* r8 ?' p4 V. n+ f创建数据库、表0 m* Q' v8 t" [$ p; `
  1. create database tests;
    ! g% ~' s1 d, P& ]$ z
  2. use tests;
    ! G9 U- V, V" `0 e9 e( h
  3. create table t_score(
    / x# K6 i: ?  P5 S% t3 f- U
  4. id int primary key auto_increment,
    $ d' `2 l2 a7 K+ w: d+ t
  5. name varchar(20) not null,  #名字1 w- r9 B: s- C) c6 U
  6. Subject varchar(10) not null, #科目& S3 k$ Z5 G" W! P# y0 @- x
  7. Fraction double default 0  #分数
    & h' j" L. N- ]2 F/ _
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    * s1 X: a& ~( F) a4 @, l* K
  2.          ('王海', '语文', 86),
    , B1 X* Y+ `3 S9 |- h7 d7 ?6 [" J
  3.         ('王海', '数学', 83),
    4 K. r$ e- \9 v& }3 _2 _" m
  4.         ('王海', '英语', 93),
    # o6 J" t( D$ N
  5.         ('陶俊', '语文', 88),# l+ C7 g+ B1 K9 O
  6.         ('陶俊', '数学', 84),
    4 o/ @/ M8 [9 j; c6 W
  7.         ('陶俊', '英语', 94),
    0 y2 n, G4 i- }% _; w* c+ Z
  8.         ('刘可', '语文', 80),
    . P, Z# N' K" C6 J
  9.         ('刘可', '数学', 86),
    # D4 M- P1 T5 ~6 p" |
  10.         ('刘可', '英语', 88),+ P7 [1 I# `! ~& U* J
  11.         ('李春', '语文', 89),
    ) Y: @9 ^$ W  w' A$ f9 [
  12.         ('李春', '数学', 80),: X$ j$ _- Z- ]7 B- y& J
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    ( R1 r: L) u" i
  2. sum(if(Subject='语文',Fraction,0)) as 语文,
    7 l: ^5 i2 m# y# X
  3. sum(if(Subject='数学',Fraction,0))as 数学, , o$ Z' Y- Q' f. G8 Z
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    " T0 X0 K  ]$ u
  5. round(AVG(Fraction),2) as 平均分,
    / r: }+ ^5 u4 I5 t, }
  6. SUM(Fraction) as 总分
    7 m' ]  F, [3 V: D( v
  7. from t_score group by name     
    - s) n0 A' ]+ y/ c, k; ?
  8. union% a. r2 ~) z' X
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(# J3 m- O7 F8 V. S. s8 {
  10. select 'TOTAL' as name,, S' p0 b6 B6 f8 r& A
  11. sum(if(Subject='语文',Fraction,0)) as 语文,2 r: y9 q2 _8 U, r* U
  12. sum(if(Subject='数学',Fraction,0))as 数学,
    ' m; B1 S; ]0 ^1 |  m' x0 K
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    6 F  o% w. s3 G/ Z# i
  14. SUM(Fraction) as 总分3 b) b" h# ~5 R: s, M/ [9 \+ S
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,
    ) o& t8 @( F* _* e
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,
    , _( Z3 L3 w. A: ^5 c9 p
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    / L4 ~! p( v0 t0 D0 R  e6 \" A
  4. sum(case when Subject = '英语' then Fraction end) as English,
    ' s' x8 u8 |0 A' k
  5. sum(fraction)as score) W  s3 B( h" U% e, x
  6. from t_score group by name
    * c+ Z1 }! F# g$ |0 U: \
  7. UNION ALL1 E0 Q! N* C: |7 Y  M
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
    2 d' V9 s, G0 ]9 b3 b! x8 c$ F( k
  9. select 'TOTAL' as name,/ j2 ~, }. I$ I/ |( t
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    ( y& {5 s9 D  @9 P
  11. sum(case when Subject = '数学' then Fraction end) as Math,& I1 G3 d8 o  o1 F5 `  N" m
  12. sum(case when Subject = '英语' then Fraction end) as English,6 z' u( _, `. q  Z% R$ D
  13. sum(fraction)as score/ J! U) c* W5 m# f2 q7 ^# ?
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    % y! d% V, e- l  ^
  2.         ifnull(name,'TOll') name,
    - M; b; J# Z+ _/ ~0 t7 q/ \  ^
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,1 N% ~# ~: o3 I4 O( j8 ]0 g- X' {$ m+ T( P
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,/ C2 g- _: e$ p% F  Q% _
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    8 O  C! t/ _0 N# H
  6.        sum(Fraction) 总分
    1 @, V, a+ Z" O8 i. R
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
( l& A9 b' ^; N: p% U
1 d" ?0 v; [% M, }7 x

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|小黑屋|召隆企博汇 ( 粤ICP备14061395号 )

GMT+8, 2025-9-18 19:45 , Processed in 0.040468 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表