召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2376|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:
- P& Z- m5 w, |7 Q7 y要求查询结果如下 :
# d( a7 c7 L, k9 C* \3 k, Q0 e" m$ E4 ?: q7 r, H9 W; v3 M

) A5 ?; Z+ G# p0 x2 z5 y) W创建数据库、表2 r. k* I1 B0 f+ S9 G& l) M
  1. create database tests;
    & A3 w$ Y% I3 @& x
  2. use tests;
    8 ]5 e7 Y% Q2 N* {" ^
  3. create table t_score(
    5 s# e+ v8 n' ~2 r& ~- j9 ]
  4. id int primary key auto_increment,
    0 A: x0 O( f. G
  5. name varchar(20) not null,  #名字
    , N0 c' i. i: G
  6. Subject varchar(10) not null, #科目& Q4 k8 V; `& d4 n' r! x
  7. Fraction double default 0  #分数
    0 A) j: q2 Q- l0 i
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES6 k! |" l! p8 p1 X( q- ^
  2.          ('王海', '语文', 86),
    2 T7 i& b3 S, H* ^4 I# V
  3.         ('王海', '数学', 83),' e9 E8 d) {, P+ f( L+ U! J) X
  4.         ('王海', '英语', 93),
    / a0 H! n# V* R$ x( J; G
  5.         ('陶俊', '语文', 88),# P3 s- ~) \" a1 h
  6.         ('陶俊', '数学', 84),
    , ~$ r' q9 y" t" Y0 N6 t
  7.         ('陶俊', '英语', 94),: [* b' l  _! S' |  b& Q
  8.         ('刘可', '语文', 80),. D; R3 m/ w  ?; Y# d( b4 b  j& H
  9.         ('刘可', '数学', 86),: E+ U, J* s# J  H
  10.         ('刘可', '英语', 88),9 X! Y% [4 X* w( m/ x) \' t
  11.         ('李春', '语文', 89),3 C. C, z# L4 B# A2 t
  12.         ('李春', '数学', 80),
    1 K' p$ ~0 g( p4 ~
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,9 i% w$ Y8 l8 l% T
  2. sum(if(Subject='语文',Fraction,0)) as 语文,
      ^$ V; S. k. Z. W5 ?
  3. sum(if(Subject='数学',Fraction,0))as 数学, % i1 ?, X7 A' N% D
  4. sum(if(Subject='英语',Fraction,0))as 英语,* o5 B4 A  |+ N: I1 B# C
  5. round(AVG(Fraction),2) as 平均分,' A- M% _- |6 C7 m2 w
  6. SUM(Fraction) as 总分
    + L8 \0 b1 ]+ T, f3 J2 |* Y
  7. from t_score group by name     ; r( I- V# D9 |9 ?% h+ {  p
  8. union
    9 [$ [' ?' H/ }( d9 b" M
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    6 ~+ T" p! `) Y) }3 D) F/ |
  10. select 'TOTAL' as name,. M" }  K/ L8 R- t: l
  11. sum(if(Subject='语文',Fraction,0)) as 语文,; W" m  K( a% h; z& M( C6 [4 E) G( E
  12. sum(if(Subject='数学',Fraction,0))as 数学, 3 j( f! G2 O' R3 g7 Y
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    ! h$ _- u/ x8 M  }1 p
  14. SUM(Fraction) as 总分, ~& J% N$ {# x5 j( J. X0 T  H; X
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,
    , H. P' M+ v+ n% t* R9 L4 U0 i
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,0 w& f% ]; c# X- N( [0 V, g
  3. sum(case when Subject = '数学' then Fraction end) as Math,) d" @+ u2 ~3 Y; ?* }  r
  4. sum(case when Subject = '英语' then Fraction end) as English,& f9 }. i5 I4 j
  5. sum(fraction)as score
    7 s( g6 n6 N8 A! v* `
  6. from t_score group by name
    % s! F( ~# S, \" Y; P9 _
  7. UNION ALL8 \0 ~; A' v- D8 M
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
    + o' y) N) w8 }3 @, l9 M
  9. select 'TOTAL' as name,
    8 ~; K8 R! c  [4 O
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,' n5 C" W. j& q" f/ r- s0 ~; S
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    : G  C0 [( X  u5 J! z) t
  12. sum(case when Subject = '英语' then Fraction end) as English,' a* \+ _3 v5 O1 c' y
  13. sum(fraction)as score6 [% ?, E9 H1 m5 z% s
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    " {# b- Z  G5 U9 n3 }) Z
  2.         ifnull(name,'TOll') name,+ s+ Z# m8 K" ]- H9 R5 V9 j
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,1 i% r9 ?8 {. U2 y
  4.        sum(if(Subject='英语',Fraction,0)) as 英语," e& M1 S. [) t( ^: B
  5.        sum(if(Subject='数学',Fraction,0))as 数学,; j( u7 E9 j- S0 |6 Q
  6.        sum(Fraction) 总分
    ' w3 S" y- P5 N" I+ u
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
" T* C, z/ ^  T3 N, O, l+ C

; a6 m) R1 Z# k

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-11-29 08:04 , Processed in 0.041030 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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