召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2319|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:% d. @: r1 |7 T4 b9 |# G6 o6 s; \, G
要求查询结果如下 :
4 q9 K  m8 M+ P  z6 ]; ~) m5 G/ A& W) j9 \* d! f

& n: l# q7 k6 k2 l创建数据库、表
! m4 _0 F& e  m" C' B  K
  1. create database tests;
    4 L+ L& R3 H; H6 C' |7 S
  2. use tests;9 x! y7 `4 \0 o2 q! ]- P
  3. create table t_score(
    ; F* t# j( Z. D, j- Y% u7 h2 I
  4. id int primary key auto_increment,6 K, @' L0 j3 {
  5. name varchar(20) not null,  #名字0 m2 R' G6 C# h' @3 t) m; ]; C* q
  6. Subject varchar(10) not null, #科目6 h; V3 N3 i8 u# r, h# X
  7. Fraction double default 0  #分数
    5 `9 y+ ?) Z$ x1 o2 ~: \
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES5 M/ c, x! M, @9 L& a
  2.          ('王海', '语文', 86),
    ; n" Y# a" _6 R8 [# ?$ r; r
  3.         ('王海', '数学', 83),
    + D* ^) }0 T* ~5 L. s; M9 W) K
  4.         ('王海', '英语', 93),
    ) B+ O' w' S' f/ `4 B
  5.         ('陶俊', '语文', 88),
    : R+ a' _; M. P4 f
  6.         ('陶俊', '数学', 84),9 D+ X8 B, }- Y6 H! ^( M
  7.         ('陶俊', '英语', 94),
    6 D0 w2 p8 ]% ]& f( u
  8.         ('刘可', '语文', 80),
    5 R8 {3 G" @9 t' h, z6 M( ^* b4 f7 G5 V
  9.         ('刘可', '数学', 86),
    1 s2 f, Y, R2 m  Y  W5 d
  10.         ('刘可', '英语', 88),; V$ J5 V4 m& x* o' S
  11.         ('李春', '语文', 89),
    % ~4 L/ e8 w6 r/ y% O9 }6 w# q
  12.         ('李春', '数学', 80),
    ! |$ K- I: M& v" F+ M; P, o$ c
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    * ~" C5 A/ ?0 C3 R4 \0 m6 S$ y- x
  2. sum(if(Subject='语文',Fraction,0)) as 语文,
    , O: i+ n5 \: w* l- X4 ~
  3. sum(if(Subject='数学',Fraction,0))as 数学, 3 \+ ~5 N* N$ X% d5 {2 e+ R8 |
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    ( ]& x  ?& F  w
  5. round(AVG(Fraction),2) as 平均分,
    0 H+ O5 r+ V( b1 U# L
  6. SUM(Fraction) as 总分
    ; ~! K  Q* ?1 n
  7. from t_score group by name     
    . e% n8 i, m$ C" `- X$ ~. ^
  8. union& r, G/ h9 d* H  k4 y$ v9 c
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    - d9 t" \$ e* z% s
  10. select 'TOTAL' as name,
    ! e7 y& y* j/ ?* P$ o) h
  11. sum(if(Subject='语文',Fraction,0)) as 语文,( X$ b8 H+ K9 a7 j
  12. sum(if(Subject='数学',Fraction,0))as 数学, 9 _/ t" \! `, y' b  E  n
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    $ [3 ]9 }+ |7 g" }, J( C' v
  14. SUM(Fraction) as 总分
    $ _: M; h, H" y0 c
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,! Y& H: Y. p. T, Z- O+ e9 g
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,$ ?" X% Z) ~8 p# |
  3. sum(case when Subject = '数学' then Fraction end) as Math,+ i9 `5 T3 `  I- |0 z5 Y! h
  4. sum(case when Subject = '英语' then Fraction end) as English,
    & g% B8 v4 D0 L4 ?' d. w
  5. sum(fraction)as score  k6 m. d* |% d, t, r. p
  6. from t_score group by name
    2 ~( R% e. n1 F8 C/ w
  7. UNION ALL( B) Y. a9 \6 c/ w) T# \
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(3 R8 P6 c* R  F1 b8 @
  9. select 'TOTAL' as name,
    # Q8 }3 K8 j' C; f6 |2 i
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,) a2 n+ t) v  S
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    # x3 m) @# d( X* r' V" p$ M2 a* N
  12. sum(case when Subject = '英语' then Fraction end) as English,
    ( o+ o1 W. |( D8 _' q; d6 y# R
  13. sum(fraction)as score
    $ E2 H5 @* u/ O6 \) M
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    # }3 U1 X0 W$ n+ C( K
  2.         ifnull(name,'TOll') name,6 A5 S! f4 T# H6 \# q9 \
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,- R1 N8 \7 I1 i  ^, C, ^7 T0 Z
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,
    ) L7 R7 A/ e% [$ U" I9 J( c
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    2 H% [: v1 V: i) [& l* O# L
  6.        sum(Fraction) 总分
    / }$ w0 s/ r2 _' q: K# y2 @
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

& H, y9 M; K( D. t
$ y5 a. w' h8 }! ]4 K( s! P

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-10-14 06:32 , Processed in 0.035640 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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