召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1843|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:
5 B: Q2 m8 B, I* J, L1 ^要求查询结果如下 :
: m& i4 I. W9 {. `) R5 H+ [0 Q1 g9 t' a; H1 V

; k8 O: O' S' p! E创建数据库、表
& N; _) M$ T" i2 u2 ~' W) `
  1. create database tests;
    ( V: Z, F" }3 w- f7 {# C2 W
  2. use tests;
    # v# g! j3 P+ P' o: A: e
  3. create table t_score(. G. m4 P' c- E( I5 q( |3 g
  4. id int primary key auto_increment,
    9 f4 E/ V3 y3 l% ]; Z. f
  5. name varchar(20) not null,  #名字" }% S8 \( M# \- v" K! L1 }
  6. Subject varchar(10) not null, #科目
    5 k/ a' g; x1 t' u4 N9 V: G! W4 k
  7. Fraction double default 0  #分数
    * e! X/ |) s2 f% H% C+ l+ U! G. H
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    * U) ~  F( ^- e1 u* K
  2.          ('王海', '语文', 86),
    6 D! K) q" B/ @5 e6 {
  3.         ('王海', '数学', 83),
    6 G- k" V1 r2 E; C
  4.         ('王海', '英语', 93),
    + t4 F9 _; @, \7 q
  5.         ('陶俊', '语文', 88),
    ! {% p" a' d, U6 M
  6.         ('陶俊', '数学', 84),
    5 M' i& A- `8 w& C; [
  7.         ('陶俊', '英语', 94),
    2 s# S* x7 E/ A" m8 N- M4 D5 t
  8.         ('刘可', '语文', 80),; g- s6 i) f! i0 ?
  9.         ('刘可', '数学', 86),8 C+ F* e2 A0 N4 ^$ C- y
  10.         ('刘可', '英语', 88),
    ' c$ L- j  u7 ?8 q
  11.         ('李春', '语文', 89),7 S2 M) m. V( T/ G5 I' r- M
  12.         ('李春', '数学', 80),
    . J" _+ f5 P% @7 d
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,& s4 a  O: I/ b
  2. sum(if(Subject='语文',Fraction,0)) as 语文,
    # _! B# _/ L9 Z9 ]  p
  3. sum(if(Subject='数学',Fraction,0))as 数学,
    1 I% K2 I6 t) ~$ U/ r9 z3 q0 [
  4. sum(if(Subject='英语',Fraction,0))as 英语,
    5 N$ G6 F! U8 S2 _0 W7 R" h1 J
  5. round(AVG(Fraction),2) as 平均分,: K5 N$ n! u- e* X7 H+ U% [
  6. SUM(Fraction) as 总分
    9 M/ N/ z) O6 l+ z" v
  7. from t_score group by name     $ e" ]3 x/ Q8 }6 O) \) J, V' d- i
  8. union
    1 I/ Q% J5 A* _. t( X. u8 e6 Q' L
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    ! m0 X3 A) A& W7 n5 Q- O
  10. select 'TOTAL' as name,
    - S! r3 G% Q3 h+ ?% \
  11. sum(if(Subject='语文',Fraction,0)) as 语文,
    0 u( p) b3 S3 T, y1 g
  12. sum(if(Subject='数学',Fraction,0))as 数学, - `9 F: }5 N( M
  13. sum(if(Subject='英语',Fraction,0))as 英语,2 R- l2 s' C; Q6 P1 J. N
  14. SUM(Fraction) as 总分) s4 }( B' O8 I/ A0 h# L) _2 J
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,
    0 u* F2 O; {( s: T: l1 ], q+ i0 x
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,
    3 |' Z0 [" g# P2 I1 _6 f
  3. sum(case when Subject = '数学' then Fraction end) as Math,: a6 E3 Y1 n* c
  4. sum(case when Subject = '英语' then Fraction end) as English,. d$ V3 F. [2 B! O
  5. sum(fraction)as score3 w( x% n- [! g0 c6 l/ U3 |
  6. from t_score group by name3 Y( |& ~) x! x1 `5 Y7 R
  7. UNION ALL  V5 V. Q8 x4 B1 Y7 y
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(4 b! C# z2 \4 t% s
  9. select 'TOTAL' as name,# _4 ]% d! h5 u$ c
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,! z" g8 \% [+ g" \
  11. sum(case when Subject = '数学' then Fraction end) as Math,9 ~! N! D2 z6 u' e, d! {
  12. sum(case when Subject = '英语' then Fraction end) as English,
    2 C& o, D3 ^: J$ T. I; d+ Y4 d$ I( `
  13. sum(fraction)as score7 u, K3 L' z. ~& f
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    ) M2 _2 |/ p: O( L; M& T5 Z
  2.         ifnull(name,'TOll') name,
    , E6 _; r* w, @; l- f8 E
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,
    / F: Q8 i* G' N) _) P
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,
    9 ^) T& w; y; X- Y+ o: X
  5.        sum(if(Subject='数学',Fraction,0))as 数学,& |8 m+ y5 W* i# d/ C- q
  6.        sum(Fraction) 总分5 i1 W$ `0 I- ^" B$ q* e) |+ S
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
; ?. s% n& a: `4 y) o

; i& b* L  S2 H1 _" B4 d

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-7-6 07:19 , Processed in 0.043476 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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