召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2378|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:
0 \8 O& A4 L! g5 d) Y" k6 [8 Y要求查询结果如下 :  t8 t. \2 r: P! ^8 h8 z
! J' a4 H2 H- s+ A0 B
" W  X" m) p+ {( J3 P
创建数据库、表
1 ~9 P8 j1 T* ]1 k5 `# l
  1. create database tests;8 ^0 c* b0 q8 n4 R8 Y
  2. use tests;0 w5 |; s, j6 C  X
  3. create table t_score(
    0 J1 P, }( r; f; q4 |% u
  4. id int primary key auto_increment,  p: n* C; z0 ^2 a
  5. name varchar(20) not null,  #名字8 r  |! W* C, k( x  ~$ R
  6. Subject varchar(10) not null, #科目
    ) y) G* h9 X! P- c$ F( B
  7. Fraction double default 0  #分数
      B9 `" d/ y/ Z3 E+ X
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
    1 J) o1 Q& p( x6 |' y" e3 g
  2.          ('王海', '语文', 86),! y5 ]  J$ U) _( b/ I8 E
  3.         ('王海', '数学', 83),
    * H& ?4 J9 J4 V
  4.         ('王海', '英语', 93),
    / u3 q0 V5 p* f/ N
  5.         ('陶俊', '语文', 88),
    % j. K* k3 \% j4 o1 d9 C
  6.         ('陶俊', '数学', 84),$ ?1 U. i4 @! }* O  ^
  7.         ('陶俊', '英语', 94),! t$ [+ y! B# }- B  i
  8.         ('刘可', '语文', 80),' b* a1 B! j. u* \
  9.         ('刘可', '数学', 86),+ Z* C) T- U7 Z3 e# n& P7 V) F) A1 _
  10.         ('刘可', '英语', 88),
      r. b- Z% b( m9 b& a
  11.         ('李春', '语文', 89)," j6 \& D: t4 B
  12.         ('李春', '数学', 80),
    , n( i( |4 x- s0 E
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,& _1 X' O  S+ z  v! j/ E# ]
  2. sum(if(Subject='语文',Fraction,0)) as 语文,8 |% \0 U7 S. i% m4 L) ~) M
  3. sum(if(Subject='数学',Fraction,0))as 数学, ( q+ r- n, a, D9 e
  4. sum(if(Subject='英语',Fraction,0))as 英语,- @# h1 b" h' p" g: h/ |* N6 i1 U
  5. round(AVG(Fraction),2) as 平均分,
    , M' O5 q7 L) I: e+ r( H% f( ~* f. x
  6. SUM(Fraction) as 总分6 p% E! A. ^- a* s- h
  7. from t_score group by name     
      T  a2 Q: R( v8 v1 P& u
  8. union+ I3 R, Q, N) l9 M, s9 D' O. u
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
    + W% Q! J$ c% i, ]& L+ B; V
  10. select 'TOTAL' as name,9 z% ?4 l! p# M
  11. sum(if(Subject='语文',Fraction,0)) as 语文,
    " ~- J# o- [2 h; r7 L7 E' m3 K, t
  12. sum(if(Subject='数学',Fraction,0))as 数学,
    - E6 D( D& |3 d$ `8 ]" o* g) Q
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    8 G2 x: {3 t0 \- [0 g, s8 ~) R3 }
  14. SUM(Fraction) as 总分" L" D8 i8 s- Z8 t( L
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,
      h! @4 G% w% X9 v) u
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,: J1 _) U8 y  d1 F; X' S+ U* L9 [: w
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    5 k8 Q: k1 C7 N7 E
  4. sum(case when Subject = '英语' then Fraction end) as English,: g- o/ X' ~0 ]7 g
  5. sum(fraction)as score; T# ~' X: O$ C& Y
  6. from t_score group by name# |, E2 E- p6 h) R9 w( v5 i
  7. UNION ALL. z5 t* L: x8 {
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
    # G# K* F4 h. O  F6 S1 {- C
  9. select 'TOTAL' as name,
    5 [+ y9 T6 x! w+ {, r$ o! a
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,1 t! _" b0 x* N: `
  11. sum(case when Subject = '数学' then Fraction end) as Math,8 _+ a( T% o$ S# s" t
  12. sum(case when Subject = '英语' then Fraction end) as English,! b6 C1 z& R$ a% U
  13. sum(fraction)as score, s  h, T' ~9 d+ b0 [" {1 j1 c. i
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    ) b5 M8 O# [+ J- m( V* r
  2.         ifnull(name,'TOll') name,% n' w. ^/ U! J. `  F% t: b
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,: g+ `/ H- S$ X
  4.        sum(if(Subject='英语',Fraction,0)) as 英语," R* S& f4 z: f; |7 M
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    9 z6 B2 W; [- O+ n' C! _
  6.        sum(Fraction) 总分& M& [) o# V6 N! C* m1 }5 H
  7.         from t_score group by name with rollup
复制代码
查询结果如下:

6 [! b& ^" r9 A
: I; l2 x; k2 Z3 c0 b# O

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-11-30 05:47 , Processed in 0.039849 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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