召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2465|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:% d9 l0 k/ L% a6 n+ h7 H- q9 m
要求查询结果如下 :
# P6 q' ]9 @1 r
1 E7 u/ c) k% s  t% b; a( ~/ c' S6 X6 W! v; i
创建数据库、表. y% F# w* y& \3 s: U: s
  1. create database tests;9 d8 H& y7 S# [; O2 v, f) c3 ^
  2. use tests;
    # r2 I$ s( T2 S) E" d, n
  3. create table t_score(* f0 a& P; q5 i4 L5 L
  4. id int primary key auto_increment,
    ( ~2 D& q& ^/ U
  5. name varchar(20) not null,  #名字! w! z3 s' m5 U' v; I7 V
  6. Subject varchar(10) not null, #科目5 N$ b+ N! w: x( G, n- P% L8 z& v0 Z
  7. Fraction double default 0  #分数  u3 W; g' b( j) f
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    9 Y/ Z$ e+ F: P0 ?* l
  2.          ('王海', '语文', 86),
    / a1 U& M4 n+ g. `. r
  3.         ('王海', '数学', 83),7 X, O1 G: p+ X( ^. k; T
  4.         ('王海', '英语', 93),
    0 S" B( A9 L8 [2 w1 y. b) m
  5.         ('陶俊', '语文', 88),$ y- ?& {/ O5 a0 Q
  6.         ('陶俊', '数学', 84),
    # o! L9 m; u5 a/ E# P
  7.         ('陶俊', '英语', 94),
    4 \& [9 I( y8 g! f- H8 ?+ O9 ]0 \
  8.         ('刘可', '语文', 80),6 [! V  A$ e  g8 ^5 s$ {* `
  9.         ('刘可', '数学', 86),$ A/ O0 g+ X7 H9 a" ?3 i; z& l
  10.         ('刘可', '英语', 88),6 o8 i! w; R+ m) e! W/ S3 G4 h' ]
  11.         ('李春', '语文', 89),
    $ B$ c) v' `, R# S( H
  12.         ('李春', '数学', 80),+ w4 u: o4 X! |% f
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    $ n% c/ V2 R! @
  2. sum(if(Subject='语文',Fraction,0)) as 语文,6 p( d( G8 S8 I( _* ]( ?# x
  3. sum(if(Subject='数学',Fraction,0))as 数学, 8 N8 ?0 Q0 {. W' `# Z( v: q4 Z
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    9 r7 Y7 Z2 U, R5 ~0 D
  5. round(AVG(Fraction),2) as 平均分,
    & z" g1 D2 B: d* A; E
  6. SUM(Fraction) as 总分1 i* ^& g  G* t5 j5 L6 u) L
  7. from t_score group by name     
    + Y8 l- F, i! w! B& g, d- {0 @2 C/ m
  8. union3 H4 v6 P( H- b6 T
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(# X; @) {" c+ t* g, i3 r
  10. select 'TOTAL' as name,$ W9 A( @  [4 r
  11. sum(if(Subject='语文',Fraction,0)) as 语文," a$ K  J6 e, S2 |; R& l
  12. sum(if(Subject='数学',Fraction,0))as 数学, 5 ?- i6 Z2 _$ d  {: W. ^
  13. sum(if(Subject='英语',Fraction,0))as 英语,0 k8 W1 i% S4 ~1 S
  14. SUM(Fraction) as 总分; v3 d% S9 x8 a  G, Y. |  Q
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,% [, N. l! g& a1 Z: {1 V" Y
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,) |2 _. z0 B9 \; }; K
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    : u1 F" ^, t" [8 o' g6 x
  4. sum(case when Subject = '英语' then Fraction end) as English,
    0 r) `! ]! P  h
  5. sum(fraction)as score
      |( H6 ]! u& V
  6. from t_score group by name9 N8 f, i- o: h3 z
  7. UNION ALL. T2 S5 h/ Y2 z. Z; Q; N& ?$ F
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
      {  w/ I, l; |) f% b) H
  9. select 'TOTAL' as name,
    ) J6 `5 P: z! T/ P$ x
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,  _: ?9 B& i; M- @3 Y- I  q
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    / s; J; Y' ?9 M/ G* ~, h) I2 z
  12. sum(case when Subject = '英语' then Fraction end) as English,
    ! a* a/ {7 o6 G" ?! h* a, A4 K
  13. sum(fraction)as score
    % H) y$ K% F# H; N1 S! Z, D
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select ' o3 X7 z6 Y/ W$ f* q
  2.         ifnull(name,'TOll') name,
    / Y! `. O& U% \2 B2 v
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,* N. K* C) ~5 F
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,
    ; H  a$ ?9 F6 W( q4 V; k& H# k
  5.        sum(if(Subject='数学',Fraction,0))as 数学,) H$ e) k  X9 l
  6.        sum(Fraction) 总分
    7 i' b1 A) ?) H# A" ?' B+ F$ P
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
$ v* [3 e! b2 [# A* x! F! ]+ l
4 Y4 t% C( i5 _$ ?% D' y

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-2-11 15:31 , Processed in 0.036808 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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