召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2335|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:; j" P0 }& \# N4 ]% |
要求查询结果如下 :8 ]& s  h3 h( s4 C" w: M+ h9 I

& d% R0 m) W% ?: I4 ]* \8 i1 z6 G+ u, K" e
创建数据库、表4 a3 u. J; q5 q0 T
  1. create database tests;
    ) w4 S9 H- K6 U
  2. use tests;/ m/ ?/ A; G: |# G+ m3 }$ R
  3. create table t_score(' m3 I/ p0 ]6 q- O
  4. id int primary key auto_increment,. p) U+ b- e: C9 O
  5. name varchar(20) not null,  #名字
    2 x" d  `  P; N
  6. Subject varchar(10) not null, #科目+ A) B- g" C3 T- `
  7. Fraction double default 0  #分数
    , L6 f4 o) B5 G
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    . J' |5 i( X  k/ @. K7 w$ h2 Z
  2.          ('王海', '语文', 86),3 c) t: Y' V7 d  t3 G' E6 e4 F9 l
  3.         ('王海', '数学', 83),5 E. R* c- H3 s3 Z0 h$ [
  4.         ('王海', '英语', 93),
    & k3 h* b( [* I/ f% r
  5.         ('陶俊', '语文', 88),
    - V% P7 M! R2 z0 G" z& B; J- p! s
  6.         ('陶俊', '数学', 84),
    + L1 C# V) x+ z
  7.         ('陶俊', '英语', 94),
    * h1 S/ ]" v& R) \( c* D
  8.         ('刘可', '语文', 80),
    ' |# [* P: T$ u$ m" ^4 L
  9.         ('刘可', '数学', 86),( }/ B5 O6 ?  P3 Z. O: w' w. k
  10.         ('刘可', '英语', 88),
    5 R4 W" l' G) z. e( l+ n' y: \
  11.         ('李春', '语文', 89),  Q3 |+ t  U* p' J1 v) @
  12.         ('李春', '数学', 80),' m# M- F5 I' f
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    ; q  X9 U+ J7 X
  2. sum(if(Subject='语文',Fraction,0)) as 语文,: c" P* W. Z. X
  3. sum(if(Subject='数学',Fraction,0))as 数学,
    4 T2 g* V$ \& k6 j; ]
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    9 l" x" `7 d% M! P& b
  5. round(AVG(Fraction),2) as 平均分,
    2 Q* c* ^  I! N0 v  f6 ]9 Q* p/ J% I5 z
  6. SUM(Fraction) as 总分
    ' M; ~: W! B/ c
  7. from t_score group by name     
    & Y4 l& `& X; N$ F, v) h- P1 v; T& e
  8. union
    ( N; Z* p* A9 X1 J4 U* Y& s1 @
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    : W8 S& Y5 c- r% A! P/ n( e) v
  10. select 'TOTAL' as name,6 F9 C$ i6 D8 |1 g  ]1 S
  11. sum(if(Subject='语文',Fraction,0)) as 语文,
    ( ^! {5 H: {4 O  |% o% s$ p$ S* P3 q
  12. sum(if(Subject='数学',Fraction,0))as 数学,   H# p2 U; n% N
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    6 j7 y9 c$ C3 K# k1 _! m
  14. SUM(Fraction) as 总分
    % ~: h1 h$ i2 }) N( X2 j: o$ K9 @
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,# ^3 M  M  a! c( H+ s( U
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,
    $ i2 L/ s2 H/ V: i) O3 g
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    . L6 o' j! o  I/ v+ f. ?* D# T
  4. sum(case when Subject = '英语' then Fraction end) as English,
    1 n6 F& e1 X* D, ~
  5. sum(fraction)as score0 @% N: W! w& d0 ?3 P  q
  6. from t_score group by name( ]9 w/ f6 Y, K, r
  7. UNION ALL
    + Q: ~. b  v; P3 u) [( {  I
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(% j/ Y3 c' U; w* ]$ c6 X* _+ o6 L
  9. select 'TOTAL' as name,
    3 V, ~4 T8 p4 x$ y! ]) K
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    ' J+ v) V& s3 W* P5 k( L; u, _$ j
  11. sum(case when Subject = '数学' then Fraction end) as Math,. s) p8 ]9 n. Q% Z- @
  12. sum(case when Subject = '英语' then Fraction end) as English,$ s: G* B5 o( G, ?
  13. sum(fraction)as score3 {$ |3 P2 ?* {' G$ _
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    ( o/ b3 {4 p% U. y
  2.         ifnull(name,'TOll') name,$ k! w7 ~1 Q7 D, P# j. d+ P! b
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,
    6 q# f! [( F3 p$ F$ V/ `* {3 ^
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,+ h- |3 v9 g& ^
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    ! O) N6 A' }! n
  6.        sum(Fraction) 总分1 A$ \/ u0 i: d/ G1 Z
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

' q: y8 g1 E7 Y: s3 z/ [0 p, x: _8 g; X) V. C

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-10-31 15:55 , Processed in 0.035791 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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