召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2381|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:" X, d* @# J  [8 v+ {: g
要求查询结果如下 :
6 u- T# c2 v8 m$ P
. l, m3 b8 R$ N* [# Z/ t! ?  y  C. Y
创建数据库、表
$ v" t0 {& d6 E* L( T! s
  1. create database tests;
    : E& e8 t2 _) d$ a; s' |% w
  2. use tests;* K5 V- o2 \, C/ h8 i8 e
  3. create table t_score(
    : C2 f! g/ V$ d& \% y- b
  4. id int primary key auto_increment,8 j( z2 l5 ~' G% A* ?) R2 I! @
  5. name varchar(20) not null,  #名字6 T) ?4 e9 A  z1 z% s% f
  6. Subject varchar(10) not null, #科目
    ) G, W% W1 @7 a2 T
  7. Fraction double default 0  #分数) z: [5 V, b5 K# M' C( t+ \/ o" Y
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES1 |, `" Y$ X3 `8 f6 A$ \$ ]9 \
  2.          ('王海', '语文', 86),  X0 N7 l  @5 K; ^3 x) C
  3.         ('王海', '数学', 83),
    2 q, J4 G& I% E
  4.         ('王海', '英语', 93),- D& }& I/ N/ v  w6 G/ [9 U' F4 ]
  5.         ('陶俊', '语文', 88),
    6 B/ ~; q0 @2 R9 `3 {+ _7 }
  6.         ('陶俊', '数学', 84),
    ' H" k: Y3 T' |* j% o- |
  7.         ('陶俊', '英语', 94),
    " y( }+ r3 N0 |: q- }, y7 f9 L
  8.         ('刘可', '语文', 80),0 U; m! ~' g3 J! [4 B2 S9 Y# ~
  9.         ('刘可', '数学', 86),
    1 V  z3 q. O( B2 d3 I0 A- J
  10.         ('刘可', '英语', 88),
    0 ?+ q* W; [. a7 ^- N1 f
  11.         ('李春', '语文', 89),& d) D6 P* o: p0 d+ G6 P( o
  12.         ('李春', '数学', 80),3 L! x9 W" i  K9 C) @" K5 z: H
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,) j$ u5 p, x3 Y# r" j+ ?
  2. sum(if(Subject='语文',Fraction,0)) as 语文,, K+ u- o, e+ o$ b
  3. sum(if(Subject='数学',Fraction,0))as 数学, 6 N) x: y+ @' `
  4. sum(if(Subject='英语',Fraction,0))as 英语,1 n: a, Q7 v8 o1 c* R
  5. round(AVG(Fraction),2) as 平均分,
    " g3 s4 I! e3 P8 x* [- c1 n
  6. SUM(Fraction) as 总分
    ( M* m- ^6 W" @% N; c/ R/ a
  7. from t_score group by name     8 N, u. X. m1 q- [
  8. union
    % a  w, a* O' b8 b3 X4 `8 a8 ?
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(% L0 g  N* A! v# `
  10. select 'TOTAL' as name,
    2 H7 \: E" L- u
  11. sum(if(Subject='语文',Fraction,0)) as 语文,8 L! W) T* U$ y+ u# G6 q
  12. sum(if(Subject='数学',Fraction,0))as 数学, 7 T; K4 z. H6 a0 M
  13. sum(if(Subject='英语',Fraction,0))as 英语,6 T8 _9 Y* F9 V+ ~
  14. SUM(Fraction) as 总分
    5 d/ A$ h: z5 }' K4 w' o4 B- G
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,
    5 y: b. D' L4 F1 ~2 M+ Z7 d
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,
    ' l& O# l3 B3 v9 I/ k* f/ m4 P
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    ( `* E) d) J' }
  4. sum(case when Subject = '英语' then Fraction end) as English,; f# V+ {5 }! P/ |) h
  5. sum(fraction)as score
    / g! G8 v" n* n% I
  6. from t_score group by name% R' N: w9 d) E# [* P
  7. UNION ALL
    ) v1 Z! b; r' [' z! x) h' V; j
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(4 V3 `) ?. x' A. o
  9. select 'TOTAL' as name,
    0 A+ y: _' a, y/ |% q$ Q
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,
      c4 G% z9 Z, u, d
  11. sum(case when Subject = '数学' then Fraction end) as Math,. ?2 l) G" G. Q$ P4 z" b; z. p! x
  12. sum(case when Subject = '英语' then Fraction end) as English,
    ; u6 j4 [. i/ E$ _7 i# O- E7 S
  13. sum(fraction)as score# ?: ?" P: K, p$ Y* M- b2 C
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select & ?, f. E- o. i9 u9 f9 |! p9 `8 Q! G. E
  2.         ifnull(name,'TOll') name,8 Y  ]- ]3 g- n, C. S& I6 Q
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,* b( j7 B' p0 {' l+ f7 p
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,
    7 n# I/ B* f7 ^% E! ~
  5.        sum(if(Subject='数学',Fraction,0))as 数学,4 n8 Z, _& ]& A+ z# q" S
  6.        sum(Fraction) 总分
    " O. l5 v. p8 Q8 H  @
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

! Q/ V0 R- Q& a$ H9 x) J' X' E6 ^4 m- r6 w$ P

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-12-1 10:16 , Processed in 0.038109 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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