精华内容
下载资源
问答
  • mysql行列转换

    2018-03-02 14:07:03
    java语言 数据库 mysql数据库 sql行列转换的例子 详解
  • Mysql行列转换

    2021-01-19 16:43:13
    Table pivot行翻转 2019年6月6日 9:16 标准化的数据库是非常强大的,在数据库的开发中,开发者需要竭尽所... 原文地址:http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ 相关文章: 1....

    Table pivot行翻转

    2019年6月6日

    9:16

    标准化的数据库是非常强大的,在数据库的开发中,开发者需要竭尽所能的完善的数据保存的方法,同时保证读写的速度,最终一般通过三泛式来解决大多数问题,有时候我们需要信息以行的方法表现但是其分布在多个列中,这是我们就需要使用行翻转来解决问题了;

    假设数据库存在以下三个表

    Users

    Items

    User_Items

    User_Items是用户以物品之间的一个多对多的关系,为了简单起见,我们不通过唯一的id来进行关连,而是人名与物品名称进行关联,第四个字段代表物品的价格;

    4ca2633bd7c325aac813721111037a48.png

    假设如果需要统计每个人所持有的单个物品的价格,类似下图

    1a92447384f1fec800fc2c660f52ab17.png

    那么表中的数据需要经过一番加工才能以这种形式表现,我们一般可以通过编程语言比如java或php来实现,或者直接通过sql来完成;

    通过sql实现行翻转需要四步

    1.选择需要的列

    2.通过选择的列对表进行扩展

    3.将表进行聚合

    4.美化

    步骤一:选择需要的列

    在此例子中我们需要找出item_type中的字段作为列的名称

    Item_amount作为行的值

    步骤二:通过选择的列对表进行扩展

    create view User_Items_Extended as (

    select

    User_Items.Cust_Names,

    case when Item_Type = "Computer" then Item_Amount end as Computer,

    case when Item_Type = "Monitor" then Item_Amount end as Monitor,

    case when Item_Type = "Software" then Item_Amount end as Software

    from User_Items

    );

    通过以上步骤我们创建了一个表的视图;

    步骤三:将表进行聚合

    create view User_Items_Extended_Pivot as (

    select

    Cust_Names,

    sum(Computer) as Computer,

    sum(Monitor) as Monitor,

    sum(Software) as Software

    from User_Items_Extended

    group by Cust_Names

    );

    通过聚合函数group我们实现了每个user都有对应的行

    步骤四:美化

    create view User_Items_Extended_Pivot_Pretty as (

    select

    Cust_Names,

    coalesce(Computer, 0) as Computer,

    coalesce(Monitor, 0) as Monitor,

    coalesce(Software, 0) as Software

    from User_Items_Extended_Pivot

    );

    展开全文
  • MySQL行列转换

    2020-06-17 10:22:40
    行转列 建表语句 create table test( id int(32) not null auto_increment, year varchar(20) default null, month varchar(20) default null, amount varchar(10) default, primary key(id) ...

    行转列

    建表语句

    create table test(
    	id int(32) not null auto_increment,
    	year varchar(20) default null,
    	month varchar(20) default null,
    	amount varchar(10) default,
    	primary key(id)
    );
    

    插入数据

    INSERT INTO test (id,year,month,amount) values (1,'2017','1','1.1');
    INSERT INTO test (id,year,month,amount) values (2,'2017','2','1.2');
    INSERT INTO test (id,year,month,amount) values (3,'2017','3','1.3');
    INSERT INTO test (id,year,month,amount) values (4,'2017','4','1.4');
    INSERT INTO test (id,year,month,amount) values (5,'2018','1','2.1');
    INSERT INTO test (id,year,month,amount) values (6,'2018','2','2.2');
    INSERT INTO test (id,year,month,amount) values (7,'2018','3','2.3');
    INSERT INTO test (id,year,month,amount) values (8,'2018','4','2.4');
    

    数据展示
    在这里插入图片描述
    行转列SQL

    SELECT
    	a.year 年份,
    	sum(
    		CASE a.`month`
    		WHEN '1' THEN a.amount
    		ELSE 0
    		END
    	) '1月',
    	sum(
    		CASE a.`month`
    		WHEN '2' THEN a.amount
    		ELSE 0
    		END
    	) '2月',
    	sum(
    		CASE a.`month`
    		WHEN '3' THEN a.amount
    		ELSE 0
    		END
    	) '3月',
    	sum(
    		CASE a.`month`
    		WHEN '4' THEN a.amount
    		ELSE 0
    		END
    	) '4月',
    	avg(a.amount) 平均,
    	sum(a.amount) 总计
    FROM
    	test a
    GROUP BY
    	a.`year`
    

    在这里插入图片描述

    列转行

    建表语句

    create table test_2
    (
    	id int(32) not null auto_increment,
    	student_name  varchar(20) default null,
    	chinese varchar(5) default null,
    	english varchar(5) default null,
    	math varchar(5) default null,
    	primary key(id)
    );
    

    插入数据

    insert into test_2 values (1,'张三','90','89','93');
    insert into test_2 values (2,'李四','95','82','78');
    insert into test_2 values (3,'王五','87','85','89');
    insert into test_2 values (4,'赵六','76','78','70');
    

    在这里插入图片描述
    列转行SQL

    select student_name,'语文' as course,chinese as score from test_2
    union select student_name,'英语' as course, english as score from test_2
    union select student_name,'数学' as course, math as score from test_2
    order by student_name,course;
    

    在这里插入图片描述

    展开全文
  • MySql行列转换

    2021-02-02 15:59:21
    mysql> select * from tx; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | A1 | B1 | 9 | | 2 | A2 | B1 | 7 | | 3 | A3 | B1 | 4 | | 4 | A4 | B1 | 2...

    数据样本:

    create table tx(

    id int primary key,

    c1 char(2),

    c2 char(2),

    c3 int

    );

    insert into tx values

    (1 ,'A1','B1',9),

    (2 ,'A2','B1',7),

    (3 ,'A3','B1',4),

    (4 ,'A4','B1',2),

    (5 ,'A1','B2',2),

    (6 ,'A2','B2',9),

    (7 ,'A3','B2',8),

    (8 ,'A4','B2',5),

    (9 ,'A1','B3',1),

    (10 ,'A2','B3',8),

    (11 ,'A3','B3',8),

    (12 ,'A4','B3',6),

    (13 ,'A1','B4',8),

    (14 ,'A2','B4',2),

    (15 ,'A3','B4',6),

    (16 ,'A4','B4',9),

    (17 ,'A1','B4',3),

    (18 ,'A2','B4',5),

    (19 ,'A3','B4',2),

    (20 ,'A4','B4',5);

    mysql> select * from tx;

    +----+------+------+------+

    | id | c1   | c2   | c3   |

    +----+------+------+------+

    |  1 | A1   | B1   |    9 |

    |  2 | A2   | B1   |    7 |

    |  3 | A3   | B1   |    4 |

    |  4 | A4   | B1   |    2 |

    |  5 | A1   | B2   |    2 |

    |  6 | A2   | B2   |    9 |

    |  7 | A3   | B2   |    8 |

    |  8 | A4   | B2   |    5 |

    |  9 | A1   | B3   |    1 |

    | 10 | A2   | B3   |    8 |

    | 11 | A3   | B3   |    8 |

    | 12 | A4   | B3   |    6 |

    | 13 | A1   | B4   |    8 |

    | 14 | A2   | B4   |    2 |

    | 15 | A3   | B4   |    6 |

    | 16 | A4   | B4   |    9 |

    | 17 | A1   | B4   |    3 |

    | 18 | A2   | B4   |    5 |

    | 19 | A3   | B4   |    2 |

    | 20 | A4   | B4   |    5 |

    +----+------+------+------+

    20 rows in set (0.00 sec)

    mysql>

    期望结果

    +------+-----+-----+-----+-----+------+

    |C1    |B1   |B2   |B3   |B4   |Total |

    +------+-----+-----+-----+-----+------+

    |A1    |9    |2    |1    |11   |23    |

    |A2    |7    |9    |8    |7    |31    |

    |A3    |4    |8    |8    |8    |28    |

    |A4    |2    |5    |6    |14   |27    |

    |Total |22   |24   |23   |40   |109   |

    +------+-----+-----+-----+-----+------+

    利用SUM(IF()) 生成列,直接生成结果不再利用子查询

    select ifnull(c1,'total'),

    sum(if(c2='B1',C3,0)) AS B1,

    sum(if(c2='B2',C3,0)) AS B2,

    sum(if(c2='B3',C3,0)) AS B3,

    sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

    from tx

    group by C1 with rollup

    动态,适用于列不确定情况

    mysql> SET @EE='';

    mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

    mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');

    Query OK, 0 rows affected (0.00 sec)

    mysql> PREPARE stmt2 FROM @QQ;

    Query OK, 0 rows affected (0.00 sec)

    Statement prepared

    mysql> EXECUTE stmt2;

    展开全文
  • mysql> select * from tx; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | A1 | B1 | 9 | | 2 | A2 | B1 | 7 | | 3 | A3 | B1 | 4 | | 4 | A4 | B1 | 2...

    这是一道行转列并且构造交叉表的问题:

    数据样本:

    create table tx(

    id int primary key,

    c1 char(2),

    c2 char(2),

    c3 int

    );

    insert into tx values

    (1 ,'A1','B1',9),

    (2 ,'A2','B1',7),

    (3 ,'A3','B1',4),

    (4 ,'A4','B1',2),

    (5 ,'A1','B2',2),

    (6 ,'A2','B2',9),

    (7 ,'A3','B2',8),

    (8 ,'A4','B2',5),

    (9 ,'A1','B3',1),

    (10 ,'A2','B3',8),

    (11 ,'A3','B3',8),

    (12 ,'A4','B3',6),

    (13 ,'A1','B4',8),

    (14 ,'A2','B4',2),

    (15 ,'A3','B4',6),

    (16 ,'A4','B4',9),

    (17 ,'A1','B4',3),

    (18 ,'A2','B4',5),

    (19 ,'A3','B4',2),

    (20 ,'A4','B4',5);

    mysql> select * from tx;

    +----+------+------+------+

    | id | c1   | c2   | c3   |

    +----+------+------+------+

    |  1 | A1   | B1   |    9 |

    |  2 | A2   | B1   |    7 |

    |  3 | A3   | B1   |    4 |

    |  4 | A4   | B1   |    2 |

    |  5 | A1   | B2   |    2 |

    |  6 | A2   | B2   |    9 |

    |  7 | A3   | B2   |    8 |

    |  8 | A4   | B2   |    5 |

    |  9 | A1   | B3   |    1 |

    | 10 | A2   | B3   |    8 |

    | 11 | A3   | B3   |    8 |

    | 12 | A4   | B3   |    6 |

    | 13 | A1   | B4   |    8 |

    | 14 | A2   | B4   |    2 |

    | 15 | A3   | B4   |    6 |

    | 16 | A4   | B4   |    9 |

    | 17 | A1   | B4   |    3 |

    | 18 | A2   | B4   |    5 |

    | 19 | A3   | B4   |    2 |

    | 20 | A4   | B4   |    5 |

    +----+------+------+------+

    20 rows in set (0.00 sec)

    mysql>

    期望结果

    +------+-----+-----+-----+-----+------+

    |Total |B1   |B2   |B3   |B4   |Total |

    +------+-----+-----+-----+-----+------+

    |A1    |9    |2    |1    |11   |23    |

    |A2    |7    |9    |8    |7    |31    |

    |A3    |4    |8    |8    |8    |28    |

    |A4    |2    |5    |6    |14   |27    |

    |Total |22   |24   |23   |40   |109   |

    +------+-----+-----+-----+-----+------+

    1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

    mysql>

    SELECT

    IFNULL(c1,'total') AS total,

    SUM(IF(c2='B1',c3,0)) AS B1,

    SUM(IF(c2='B2',c3,0)) AS B2,

    SUM(IF(c2='B3',c3,0)) AS B3,

    SUM(IF(c2='B4',c3,0)) AS B4,

    SUM(IF(c2='total',c3,0)) AS total

    FROM (

    SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3

    FROM tx   GROUP BY c1,c2

    WITH ROLLUP

    HAVING c1 IS NOT NULL

    ) AS A

    GROUP BY c1

    WITH ROLLUP;

    +-------+------+------+------+------+-------+

    | total | B1   | B2   | B3   | B4   | total |

    +-------+------+------+------+------+-------+

    | A1    |    9 |    2 |    1 |   11 |    23 |

    | A2    |    7 |    9 |    8 |    7 |    31 |

    | A3    |    4 |    8 |    8 |    8 |    28 |

    | A4    |    2 |    5 |    6 |   14 |    27 |

    | total |   22 |   24 |   23 |   40 |   109 |

    +-------+------+------+------+------+-------+

    5 rows in set, 1 warning (0.00 sec)

    2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

    mysql>

    select c1,

    sum(if(c2='B1',C3,0)) AS B1,

    sum(if(c2='B2',C3,0)) AS B2,

    sum(if(c2='B3',C3,0)) AS B3,

    sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

    from tx

    group by C1

    UNION

    SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,

    sum(if(c2='B2',C3,0)) AS B2,

    sum(if(c2='B3',C3,0)) AS B3,

    sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX;

    +-------+------+------+------+------+-------+

    | c1    | B1   | B2   | B3   | B4   | TOTAL |

    +-------+------+------+------+------+-------+

    | A1    |    9 |    2 |    1 |   11 |    23 |

    | A2    |    7 |    9 |    8 |    7 |    31 |

    | A3    |    4 |    8 |    8 |    8 |    28 |

    | A4    |    2 |    5 |    6 |   14 |    27 |

    | TOTAL |   22 |   24 |   23 |   40 |   109 |

    +-------+------+------+------+------+-------+

    5 rows in set (0.00 sec)

    mysql>

    3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询

    mysql>

    select

    ifnull(c1,'total'),

    sum(if(c2='B1',C3,0)) AS B1,

    sum(if(c2='B2',C3,0)) AS B2,

    sum(if(c2='B3',C3,0)) AS B3,

    sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

    from tx

    group by C1 with rollup ;

    +--------------------+------+------+------+------+-------+

    | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |

    +--------------------+------+------+------+------+-------+

    | A1                 |    9 |    2 |    1 |   11 |    23 |

    | A2                 |    7 |    9 |    8 |    7 |    31 |

    | A3                 |    4 |    8 |    8 |    8 |    28 |

    | A4                 |    2 |    5 |    6 |   14 |    27 |

    | total              |   22 |   24 |   23 |   40 |   109 |

    +--------------------+------+------+------+------+-------+

    5 rows in set (0.00 sec)

    mysql>

    4. 动态,适用于列不确定情况,

    mysql> SET @EE='';

    mysql> SELECT @EE :=CONCAT(@EE ,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

    mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE ,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');

    Query OK, 0 rows affected (0.00 sec)

    mysql> PREPARE stmt2 FROM @QQ ;

    Query OK, 0 rows affected (0.00 sec)

    Statement prepared

    mysql> EXECUTE stmt2;

    +--------------------+------+------+------+------+-------+

    | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |

    +--------------------+------+------+------+------+-------+

    | A1                 |    9 |    2 |    1 |   11 |    23 |

    | A2                 |    7 |    9 |    8 |    7 |    31 |

    | A3                 |    4 |    8 |    8 |    8 |    28 |

    | A4                 |    2 |    5 |    6 |   14 |    27 |

    | total              |   22 |   24 |   23 |   40 |   109 |

    +--------------------+------+------+------+------+-------+

    5 rows in set (0.00 sec)

    mysql>

    其实数据库中也可以用 CASE WHEN / DECODE 代替 IF

    sum(if(c2='B1',C3,0)) AS B1

    可改写为

    sum(case c2 when 'B1' then C3 else 0 end) AS B1

    展开全文
  • mysql 查询行列转换

    2019-07-29 01:43:00
    NULL 博文链接:https://chenyuxiaoxiao.iteye.com/blog/831674
  • MySQL行列转换函数 – group_concat 1、环境搭建 CREATE TABLE temp_dep( id INT, depId INT, name varchar(20), age INT, address varchar(100), brithday date ); INSERT INTO temp_dep(id, depId, name, ...
  • mysql 行列转换

    千次阅读 热门讨论 2020-12-30 12:16:22
    可以直接求比例了 但是,我们需要的是将药敏显示到行上,直接求比不符合需求,所以我们需要将列转换为行 我们借助于case when实现行列转换,并将药敏结果根据字典转为方便阅读的汉字 SELECT C.project_name 项目名称...
  • 一段sqlserversql代码完成行列转换,但在mysql里无法执行,请问mysql的应该怎么写?createtableA1(NO1int,NO2varchar(10),MYA1int,MYA2int,MYA3int,MYA4int,MYA5int,MYA6int)insertA1...一段sqlserver sql代码完成...
  • 数据表: 第一种方法:CASE WHEN THEN ELSE END SELECT `name`, MAX( CASE WHEN course='语文' THEN score END ) AS 语文, MAX( CASE WHEN course='数学' THEN score END ) AS 数学, ...G
  • MySQL 行列转换

    2017-02-10 14:54:17
    最近在慕课上 看MySQL教程 里面关于行转列的教程不错 贴上练习SQL 做个记录 简单行转列SELECT a.user_name, sum(b.kills) FROM user1 a JOIN user_kills b ON a.id = b.user_...CROSS JOIN 行列转换select * from
  • 方法一:   max()是group by的时候取有值的那一条...mysql字符串大小比较:使用MAX()查询一个字符串类型的字段时,字符串类型大小比较是先比较首字符的ASCII码的大小,然后依次往后进行比较的。   方法二: ...
  • MYSQL行列转换详解

    2021-01-03 19:04:58
    MYSQL行列转换详解 (1)首先可以想到根据姓名来进行分组 select 姓名 from scores group by 姓名 (2)接着使用case when方法 显示我们的 ‘'课程" 字段 select 姓名, sum(case 课程 when ‘语文’ then 分数 ...
  • mysql行列转换详解

    2020-12-19 19:52:44
    面试老是会考到这样一题: 将下面表中的语文,英语,数学作为行,分数总和...即转换为这样的表结构: 转换语句: SELECT t.name , SUM( IF ( t.subject = '语文', t.results, 0 ) ) AS '语文', SUM( IF

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,608
精华内容 2,643
关键字:

mysql行列转换

mysql 订阅
友情链接: WaterJXC.rar