召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2382|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:- ^  Y# J2 O+ Z. O( J2 A
要求查询结果如下 :( B: v& }5 P3 Q1 M0 }0 @

6 ?  v( b0 V  Y4 a1 l: s, N  J: e+ |* d8 `, [7 ]
创建数据库、表3 C4 O0 Y$ T  E9 c% j
  1. create database tests;
    : g4 f! z9 }. u3 D6 [
  2. use tests;
    3 u5 f7 h% R/ R- k0 B& ~9 D0 X, J6 h
  3. create table t_score(
    $ j+ O5 _6 P( @( x% U* i# }) U; f
  4. id int primary key auto_increment,
    % J- u3 a$ p( l+ p  X
  5. name varchar(20) not null,  #名字, E, u, t( {* Q
  6. Subject varchar(10) not null, #科目
    . T" o6 V4 i$ j, N2 `: U
  7. Fraction double default 0  #分数
    3 W/ s! k2 T% p0 f3 I" V
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES, k3 R5 z8 O; \& L5 r9 Y5 r
  2.          ('王海', '语文', 86),) Y6 p. h+ i: z8 N& Y. h
  3.         ('王海', '数学', 83),
    ) ]0 E7 ?3 J+ R$ P, Y
  4.         ('王海', '英语', 93),
    ; J$ e" ?; g9 ^) G7 [1 y- V, k
  5.         ('陶俊', '语文', 88)," Y2 U) R8 S) s& p& Q
  6.         ('陶俊', '数学', 84),
    & u" `2 u  a; H
  7.         ('陶俊', '英语', 94),2 S+ @! d' g; G' ^0 |8 R
  8.         ('刘可', '语文', 80),
    4 n. N% A4 }; x7 G9 l
  9.         ('刘可', '数学', 86),
      U- c  ^8 W# v6 S; v; ]
  10.         ('刘可', '英语', 88),
      K5 I& _$ R4 K; }, @
  11.         ('李春', '语文', 89),: e2 J5 v" |! P7 ~( Z
  12.         ('李春', '数学', 80),8 [) b4 U# s. r3 G: R' X# E+ B! b
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    - K; @* {+ H% F$ l
  2. sum(if(Subject='语文',Fraction,0)) as 语文,# H: g. L+ `' _1 A/ J
  3. sum(if(Subject='数学',Fraction,0))as 数学,
    ! }5 ?% t/ _  Q: u4 K/ F
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    7 t' Z- R6 f: @
  5. round(AVG(Fraction),2) as 平均分,
    ; s( Z1 Z. W( @2 u0 j
  6. SUM(Fraction) as 总分/ z+ ~+ n0 H# i9 s
  7. from t_score group by name     . k+ ]1 u6 ~: j# @
  8. union
    & V; A% N2 G* S
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    & T: N' t, X5 x5 |8 r
  10. select 'TOTAL' as name,) g1 i" x# V+ Z* m$ D
  11. sum(if(Subject='语文',Fraction,0)) as 语文,. }& n4 A! B0 X& w% h( p
  12. sum(if(Subject='数学',Fraction,0))as 数学,
    1 F' ]8 S; f1 }4 l& x) I! W
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    7 _& u, r& q4 A1 t0 q; V
  14. SUM(Fraction) as 总分! ?, a4 X/ k  h% C7 `; ^
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,% ~1 ?# x- B9 O0 X/ M+ K% z
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,. w% f9 I) d: a
  3. sum(case when Subject = '数学' then Fraction end) as Math,& P8 C% m4 e- P8 v1 }, v9 P
  4. sum(case when Subject = '英语' then Fraction end) as English,% x1 S. B5 h+ _+ J
  5. sum(fraction)as score  ?8 j5 g5 \+ B- c$ V
  6. from t_score group by name
    - k/ u) i1 y% w5 T$ N
  7. UNION ALL+ \5 p# B0 v8 O0 }7 |
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
    0 `, [) H" o# r9 M# e
  9. select 'TOTAL' as name,$ g3 x+ M4 E, ]+ A3 \
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,3 w& r, d& B1 i- X# a; X8 S
  11. sum(case when Subject = '数学' then Fraction end) as Math,
    - w$ t* e5 v  e, C8 {
  12. sum(case when Subject = '英语' then Fraction end) as English,
    ! g, ~1 T/ B* z8 L0 w3 _& `
  13. sum(fraction)as score
    - b. d6 w7 A0 u2 @6 U  m1 z
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    & [1 L2 ~: Z) D
  2.         ifnull(name,'TOll') name,2 ?! T7 M. E; G& o
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,6 w5 m5 t. ^; ~: a. C2 D* g
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,8 O) p. J) b/ s5 K/ B) ]- T0 Q$ b* G& F
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    + O5 z. Q" H. V" D9 V0 D" d( Q5 b
  6.        sum(Fraction) 总分4 V7 Q; r& D4 _2 R7 |7 ?- a
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

/ b1 }1 Z1 S# `9 i' _- S2 Q* i2 Z8 I6 {/ @4 u! a

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-12-1 22:51 , Processed in 0.036656 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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