召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 743|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:
7 f5 @; q, c' Y% h5 J' L要求查询结果如下 :4 a% |+ l2 d' t: k

9 a$ i# v) p- g
, Z" s7 M- ^+ R; x* g$ ?3 F创建数据库、表! h' ~3 _/ H% n: Z# ^
  1. create database tests;9 h7 u1 v0 j2 o, S4 W0 o  B9 j$ i
  2. use tests;
      ^% Q8 x( ~3 N6 h1 l" w$ O, h5 {
  3. create table t_score(7 K2 b  ]/ m7 ~6 m
  4. id int primary key auto_increment,) r# a) m/ J+ K6 O7 `# k
  5. name varchar(20) not null,  #名字
    $ @. _2 o6 E8 R& U
  6. Subject varchar(10) not null, #科目
    ( r/ f. z7 F( D& R! [
  7. Fraction double default 0  #分数
    4 B9 V5 j, H) C
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES' M: S6 Y0 X& B% U3 C, o* S! V
  2.          ('王海', '语文', 86),  Q8 {( z8 c  P( @
  3.         ('王海', '数学', 83),. I* B" f8 q, A6 \* y" h
  4.         ('王海', '英语', 93),: W5 k# ~9 i9 l# h8 N
  5.         ('陶俊', '语文', 88),2 R' J& b3 L" r# j
  6.         ('陶俊', '数学', 84),' ], |# g6 l2 i9 M+ q" z1 R
  7.         ('陶俊', '英语', 94),7 x. I- \  K" d3 A3 @1 G
  8.         ('刘可', '语文', 80),
    . N, z8 s: \0 }1 e3 X* v' f$ R
  9.         ('刘可', '数学', 86),
    " I. H2 ~4 E- W6 @( p/ {2 y
  10.         ('刘可', '英语', 88),# y. m1 o( i! J% l: E
  11.         ('李春', '语文', 89),# Z( G/ D* r% H2 {4 m% J# P
  12.         ('李春', '数学', 80),
    * p/ J2 S: |* Y- Y6 R
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,( Z/ j; b, d# w  S$ H" ?: S3 l$ v
  2. sum(if(Subject='语文',Fraction,0)) as 语文,( n$ {; \" b7 W! x7 R
  3. sum(if(Subject='数学',Fraction,0))as 数学,
    8 a- G/ j0 Q& r. }7 Q1 K8 U
  4. sum(if(Subject='英语',Fraction,0))as 英语,
      x& g+ Q9 \. D1 J5 Q
  5. round(AVG(Fraction),2) as 平均分,
    . ?7 `3 X; K6 L2 i3 D% @
  6. SUM(Fraction) as 总分
      Q1 Q+ _; `" X1 W
  7. from t_score group by name     
    ; }- V) Q, G: Q; r8 A
  8. union
    3 I; B/ C- K+ ~" T* F% c
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(% z8 p% H4 Y* G5 ^
  10. select 'TOTAL' as name,
    0 l6 m. J6 v7 z- C$ B$ c
  11. sum(if(Subject='语文',Fraction,0)) as 语文,
    ) p/ ~1 k: |0 ^' d* M8 Z
  12. sum(if(Subject='数学',Fraction,0))as 数学, + ?; X8 `* r( F
  13. sum(if(Subject='英语',Fraction,0))as 英语,( m7 D! D/ `/ R/ _7 o+ c
  14. SUM(Fraction) as 总分7 A: ]$ k5 E- y. u" H% @  e4 x5 r
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,& f& M2 H4 K* ^) ?# J
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,4 S2 F, Q* }' y5 y
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    ! L5 x: }3 S- Q8 {% f
  4. sum(case when Subject = '英语' then Fraction end) as English,/ f/ g. j$ I: \: J( w$ ~
  5. sum(fraction)as score+ G) R6 Y* g; f% R
  6. from t_score group by name/ I* }; n1 i6 J2 X/ k5 \
  7. UNION ALL
    ( a7 a! _9 @& G
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
    ' {! A+ z, @: C7 ~, H7 D# v2 b
  9. select 'TOTAL' as name,7 ?2 S! a2 W% e. V
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,2 x4 ]5 p, Q& s. {" Y7 m
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    ! X+ q4 k! n" W8 |) k% u( `
  12. sum(case when Subject = '英语' then Fraction end) as English,0 i! j/ `3 s$ I" e/ K* C7 d* M, F
  13. sum(fraction)as score
    2 m2 z- `! a$ `  X
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select " ^0 r7 q8 b4 w5 u: W/ S
  2.         ifnull(name,'TOll') name,
    # I4 ]9 @( i& W0 Y
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,
    5 p. `$ s: J0 p5 [
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,/ `0 ]* R9 u" r; F+ ~
  5.        sum(if(Subject='数学',Fraction,0))as 数学,7 G9 ~$ ]2 J' ?
  6.        sum(Fraction) 总分
      c5 E# p+ e! ^$ X. F' }; R5 {' h8 T
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

! _' p( ?# h0 u5 O* X1 B- M; G0 d5 f% l  [5 W5 a. q" p

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-4-2 13:40 , Processed in 0.031464 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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