召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1342|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:" s$ d& X1 }4 h% d" U
要求查询结果如下 :
. N# ?! N8 f- g' s( O/ b% X0 W1 }! `- U; G$ C1 N" \4 K
0 q3 p2 I8 ~# s" S; P1 i
创建数据库、表
7 I: ?- o( X1 s4 |% W5 _7 Q" C
  1. create database tests;% P' u: I! s  k, v9 ~
  2. use tests;2 S& {2 h5 H1 T$ P9 }; G. m: J
  3. create table t_score(
    9 i6 m/ m! P! i
  4. id int primary key auto_increment,2 k/ \2 {* H& L& t
  5. name varchar(20) not null,  #名字& A/ z) Z! Z. t  u0 H$ v
  6. Subject varchar(10) not null, #科目
    ) n8 O2 x) E* `
  7. Fraction double default 0  #分数
    + \7 O- b: R# N! }1 \6 B2 a
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    / |1 y# `* i6 O# c8 K
  2.          ('王海', '语文', 86),0 J4 Q/ k& W2 J: V& \8 j
  3.         ('王海', '数学', 83),
    4 s' u# l" E1 J) X* A
  4.         ('王海', '英语', 93),% I) B7 Q! [( L' q& }7 v; M
  5.         ('陶俊', '语文', 88),/ w( K# ]* Q: y/ i
  6.         ('陶俊', '数学', 84),# C. B" @5 B% b' [
  7.         ('陶俊', '英语', 94),
    - Y6 @: b8 d# p& q! \# c
  8.         ('刘可', '语文', 80),
    & @" _8 u6 y. j2 ^$ r+ B8 o) j6 J
  9.         ('刘可', '数学', 86),
    7 N0 r% ^8 f- a& _2 I6 s6 m
  10.         ('刘可', '英语', 88),% j/ N% o+ h6 ~( j9 C: q
  11.         ('李春', '语文', 89),
    + j2 |1 B! _' H9 M2 `) o
  12.         ('李春', '数学', 80),
    7 a" l2 }" n6 h! w
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,+ N7 r) X3 ~8 J0 Q5 ^
  2. sum(if(Subject='语文',Fraction,0)) as 语文,8 J  C5 Q; a. I* o/ I
  3. sum(if(Subject='数学',Fraction,0))as 数学, ) v% @/ N4 E$ R
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    + Y& k3 O% b0 Y# J! y
  5. round(AVG(Fraction),2) as 平均分,& k* `* u! |2 S; f! `/ ^, j5 P
  6. SUM(Fraction) as 总分
    + r0 l# b2 K8 a7 O* {
  7. from t_score group by name     
    ; u; g0 `* x7 B9 G% v1 L0 C. h% K
  8. union
    + f3 Z7 s6 {/ s
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(2 [& P! l; Y' {1 q
  10. select 'TOTAL' as name,  B7 ]3 }) E% I) g" U, `
  11. sum(if(Subject='语文',Fraction,0)) as 语文,$ a+ j6 U) O: ~: c$ I
  12. sum(if(Subject='数学',Fraction,0))as 数学,
      E1 x: ?. L1 s' d
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    - Z% g$ `2 l7 I; Z
  14. SUM(Fraction) as 总分3 A' ?' b8 s: h
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,$ @0 m7 B% y( K& A) K7 s* d" \
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,  ~; r! z! T/ T! N$ c/ ?) M
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    8 {, H- e3 {8 V& m) o$ K
  4. sum(case when Subject = '英语' then Fraction end) as English,
    1 I0 M! k- E, c0 y( m, O
  5. sum(fraction)as score3 t: X; k/ r' X+ j' o; s+ o" V: D
  6. from t_score group by name
    6 V. [- s6 R; J# y: ?" e
  7. UNION ALL1 }0 n5 |1 e: |* z8 d: e
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(! U8 J( P4 V* i' L8 ?& P/ V
  9. select 'TOTAL' as name,
    & e" P$ Z. d/ b! ^/ y2 m+ w
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,! y# L& u: K  M. \$ k
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    / c# K: ?7 k8 E' I4 V0 ^! v' Q
  12. sum(case when Subject = '英语' then Fraction end) as English,
    / W, R2 Z& _% ], A
  13. sum(fraction)as score. w- a# y! |2 P7 x& Z% B
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select + N+ f: m6 K" V- K7 \
  2.         ifnull(name,'TOll') name,3 |5 {7 n' E; E: v. B6 u
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,
    - C) N$ ?$ B; T! N% o
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,; [* d2 L% c) u( z; |2 ?; U
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    ( x' ^: k4 n4 c2 Z9 r" k8 L
  6.        sum(Fraction) 总分) X  b' T$ ?1 H* t5 o% w0 b; ?
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
( r- ?: K5 b* a9 \
' X5 H) x5 l% r0 O; {" w

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-6-2 03:04 , Processed in 0.035569 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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