召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1742|回复: 0

mysql行列转换

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:14:57 | 显示全部楼层 |阅读模式
原始插入数据如下:" v- [, n- R# F8 ?1 i( a, g9 [$ [
要求查询结果如下 :
4 I2 @! F  J) X) }8 i4 L" }. B  u9 }4 y
8 ]: i. K. V3 g. t2 p9 Z, R
创建数据库、表( l! {! M5 m  _8 `
  1. create database tests;
    7 e5 n  \6 S5 I5 w2 C' Y4 P9 R
  2. use tests;$ _+ z1 Y! O: `. O- M
  3. create table t_score(
    ( H1 ]5 Z1 R- e
  4. id int primary key auto_increment,- P9 a$ d9 g; i
  5. name varchar(20) not null,  #名字
    - Z) ]# q6 O% V" Z: K
  6. Subject varchar(10) not null, #科目: E" ~  o6 o' R7 o
  7. Fraction double default 0  #分数. w( \9 |$ r( @& E
  8. );
复制代码
添加数据
  1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES% ]$ N, D* W9 B0 Q/ P& y: Z
  2.          ('王海', '语文', 86),
    4 M6 F/ `: p( d# u+ R" k
  3.         ('王海', '数学', 83),% i- `+ a, z8 v1 A
  4.         ('王海', '英语', 93),
    * V2 n! T; D0 Z2 m5 ^# r0 c
  5.         ('陶俊', '语文', 88),5 i' j% g9 z6 N* g
  6.         ('陶俊', '数学', 84),
    # X, `( x& |6 e. j, q
  7.         ('陶俊', '英语', 94),1 L' T9 B2 }7 u- c5 t- d
  8.         ('刘可', '语文', 80),; j! V4 g7 A3 ~% l
  9.         ('刘可', '数学', 86),
    1 `4 d  V! J3 k6 S0 k% H3 c
  10.         ('刘可', '英语', 88),7 r; w( V9 I9 v6 A! H# h+ [' R
  11.         ('李春', '语文', 89),
    ( z, L3 v. H: ]9 u; G9 X
  12.         ('李春', '数学', 80),8 g6 _7 ?% T- {# H) R: q- F- X
  13.         ('李春', '英语', 87);
复制代码
方式一:使用if
  1. select name as 名字 ,
    . L2 I5 ~( @1 z" h" l
  2. sum(if(Subject='语文',Fraction,0)) as 语文,: P/ ]  q. k' L+ E$ g8 i" }7 }
  3. sum(if(Subject='数学',Fraction,0))as 数学, : o3 k* K9 b* B! |
  4. sum(if(Subject='英语',Fraction,0))as 英语," K' E+ T' S7 S: K6 X' E
  5. round(AVG(Fraction),2) as 平均分,
    7 M2 Q* M- \4 Q* C
  6. SUM(Fraction) as 总分
    " y& c$ S, p* F4 U6 V& }9 }
  7. from t_score group by name     ( w: B6 @/ R% m, J+ _  ?
  8. union
    * C5 [! B, k; O. K
  9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(9 d, s" N: h; f7 x2 i( f
  10. select 'TOTAL' as name,
    1 H* v+ v9 ^& D9 x8 P
  11. sum(if(Subject='语文',Fraction,0)) as 语文,
    ) J2 {/ G) l: }: t8 i- ]5 D
  12. sum(if(Subject='数学',Fraction,0))as 数学, ( a7 g, B8 g0 ^, F, |. X1 v
  13. sum(if(Subject='英语',Fraction,0))as 英语,
    : z2 z* d( h& \  v3 n* n+ C
  14. SUM(Fraction) as 总分0 v9 I) u- E4 G0 F
  15. from t_score group by Subject )t
复制代码
方式二:使用case
  1. select  name as Name,- z, _# L0 [$ c+ W  q$ _
  2. sum(case when Subject = '语文' then Fraction end) as Chinese,% U/ X' l) [( q  l6 N6 W2 t/ }: r2 x: v
  3. sum(case when Subject = '数学' then Fraction end) as Math,
    7 n3 T- h8 A! k1 E0 U( A8 `
  4. sum(case when Subject = '英语' then Fraction end) as English," L" D# T% s, n! a* x
  5. sum(fraction)as score
    7 \3 ~) K  j1 q6 g& k
  6. from t_score group by name+ t2 r. z) g1 {1 ^- Z
  7. UNION ALL
    6 l" d; c0 L! u) u; Y
  8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(4 i# f! Q, Q- I
  9. select 'TOTAL' as name,
    / j; k) y3 d8 e) E7 c) T$ N7 M
  10. sum(case when Subject = '语文' then Fraction end) as Chinese,$ o* R6 c! H9 E# e% d
  11. sum(case when Subject = '数学' then Fraction end) as Math," a' A6 k) Z) ]
  12. sum(case when Subject = '英语' then Fraction end) as English,
    8 ^5 C& q( X. f% J' v
  13. sum(fraction)as score
    9 G+ r7 @/ W9 P6 P! [. ]
  14. from t_score group by Subject)t
复制代码
方法三: with rollup
  1. select
    : |9 ]  S& Z' X; W* g1 l* k# E  S
  2.         ifnull(name,'TOll') name,
    : Y6 |% t% {& r. t1 {  a  X
  3.         sum(if(Subject='语文',Fraction,0)) as 语文,9 V: f. z8 }, @1 P% \! M/ |
  4.        sum(if(Subject='英语',Fraction,0)) as 英语,
    ' X. U! `' _0 i
  5.        sum(if(Subject='数学',Fraction,0))as 数学,
    . S' J- p  z  O9 `' E, Y
  6.        sum(Fraction) 总分* l) {0 x, Z, ~! p3 A* {7 X
  7.         from t_score group by name with rollup
复制代码
查询结果如下:
, [! }0 e& R4 I) k" V& e* V% {

% O+ J# G  G" w% L3 d

本帖子中包含更多资源

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

x
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-6-27 09:37 , Processed in 0.043419 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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