精华内容
下载资源
问答
  • 列转行:利用max(case when then) max—聚合函数 取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN ...
  • mysql 列转行

    2021-03-09 20:32:03
    pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT value AT BT CT DT ET SELECT DISTINCT t.id, t.role_id FROM ( SELECT ... SUBSTRING_INDEX( SUBSTR.
    pkvalue
    1ET,AT
    2AT,BT
    3AT,DT
    4DT,CT,AT

     

    value
    AT
    BT
    CT
    DT
    ET
     SELECT DISTINCT
            t.id,
            t.role_id
            FROM
            (
            SELECT
            a.id,
            t.role_id
            SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id
            FROM
            blade_user a
            LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

    思路:

    1. substring_index函数对字符串进行截取
    2. 通过substring_index函数特性,我们就需要知道字符串有多少个逗号,并且要告诉每个逗号的位置
    3. 逗号个数=LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) 
    4. 逗号位置=b.id< 逗号个数[+1]
    5. 最后通过distinct函数将截取后的单个值进行去重

     

    说明:help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 )

    不明白为什么查询条件要小于这个字段的 字符串个数?

    举例:

    表blade_user:

    id       role_id

    1          ET,AT

    2          AT,BT

    表help_index

    id

    1

    2

     

    SELECT DISTINCT
            t.id,
            t.role_id
            FROM
            (
            SELECT
            a.id,
            t.role_id
            FROM
            blade_user a
            LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

    查询结果 

    pk       value             id

    1          ET,AT            1

    1          ET,AT            2

    2          AT,BT             1

    2          AT,BT             2

     查询顺序是先关联,然后select 

    1、所以把符合条件的两个表查询出来,

    每次执行select    SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id

    这里查询会先取b.id的值,所以就自然筛选出   a.role_id 的  第1个,第2个。

     

    查询结果

     SELECT DISTINCT
            t.id,
            t.role_id
            FROM
            (
            SELECT
            a.id,
            t.role_id
            SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id
            FROM
            blade_user a
            LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

     

     

    pk       value             id

    1          ET            1

    1          AT            2

    2          AT             1

    2          BT             2

     

    展开全文
  • 如下所示: SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,  DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  FROM  表名 a  WHERE  a.statu = 3  AND a.rstatu = 2  AND a.job_dept IN ('19', '...
  • mysql 列转行列转行的问题今天在逛大java吧的时候看到一个行转列、列转行的问题,看了之后还真的不知道怎么下手,可能平时用hibernate作为持久层用多了,sql语句也不会写了,赶紧去找度娘聊聊天,然后建了个表做个...

    mysql 列转行、列转行的问题

    今天在逛大java吧的时候看到一个行转列、列转行的问题,看了之后还真的不知道怎么下手,可能平时用hibernate作为持久层用多了,sql语句也不会写了,赶紧去找度娘聊聊天,然后建了个表做个测试,趁着这会儿还没忘改进总结一下,省的回头又忘了。

    首先需要新建一个表:

    DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `Id`int(11) NOT NULL AUTO_INCREMENT, `ye` varchar(20) DEFAULT NULL, `me` varchar(20) DEFAULT NULL, `Scount`doubleDEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

    插入几条数据后效果如下所示:

    2c97d252e944c6d03676ea6cd69905f3.png

    最后要实现的效果是什么样的呢?如下所示:

    10

    要达到的效果就是如上所示的这样,那么怎么实现呢?SQL语句如下所示:

    SELECT ye, (MAX(CASE me WHEN'1' THEN Scount ELSE 0END)) AS M1, (MAX(CASE me WHEN'2' THEN Scount ELSE 0END)) AS M2, (MAX(CASE me WHEN'3' THEN Scount ELSE 0END)) AS M3, (MAX(CASE me WHEN'4' THEN Scount ELSE 0END)) AS M4 FROM test1 GROUP BY ye;

    我暂时就这样写了,应该还有其他的方法,这个里面还得再说明几点;

    1.case me when '1' then Scount else 0 end 其实就是一个条件判断,等同于 if(me == '1'){Scount}else{0},因此也可以使用SQL中的IF表达式来代替这个表达,使用if表达式怎么写呢?看下面:

    SELECT ye, (MAX(IF(me='1',Scount,0))) AS M1, (MAX(IF(me='2',Scount,0))) AS M2, (MAX(IF(me='3',Scount,0))) AS M3, (MAX(IF(me='4',Scount,0))) AS M4 FROM test1 GROUP BY ye;

    if(expr1,expr2,expr3)表达式中,如果expr1表达式为true,那么结果就是expr2,如果expr1表达式为false,那么结果就是expr3;

    2.在看列转行的时候看到这么一个函数group_concat(),觉得挺好玩的,就也记录一下吧:

    SELECT ye, GROUP_CONCAT(me,'数量为:',Scount) AS M FROM test1 GROUP BY ye;

    得到的结果如下:

    10

    其实就是将三个字段组合到了一起,这个函数具体用法为group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']),即还可以对字段进行排序以及使用特定的符号分隔数据;

    说到group_concat了,那就把相关的concat()也给复习一下吧,concat(str1,str2,str3.....)就是把后面的str进行连接到一起,看如下例子:

    SELECT CONCAT("aa","bb","cc") AS result;

    查询结果如下:

    10

    但是str不能使null,如果是null的话,那么返回的结果就是null,例子我就不写了。

    下一个是concat_ws(Separator,str1,str2.....),这个函数就是使用第一个分隔符把后面的str连接起来,这个分隔符可以使符号也可以使字符串,下面给个例子:

    10

    可以看到,三个字符串使用_888_进行了连接,在这个函数中,Separator可以为空字符串,但是不能为NULL,否则结果也是NULL,str可以为Null.

    关于列转行,就使用concat的方式来进行查询,先对刚才的数据表进行一下修改,表结构不变,把数据修改一下,如下:

    10

    此时,按照年份求和,sql语句如下所示:

    SELECT t.ye, CONCAT('1的总和',CAST(SUM(CASE t.me WHEN '1' THEN t.Scount ELSE 0END) AS CHAR) ) AS M1, CONCAT('2的总和',CAST(SUM(CASE t.me WHEN '2' THEN t.Scount ELSE 0END) AS CHAR) ) AS m2 FROM test1 t GROUP BY t.ye;

    查询结果如下:

    10

    好了,行转列,列转行暂时就这样了,下次再见。

    欢迎大家阅读《mysql 列转行、列转行的有关问题_mysql》,跪求各位点评,by 搞代码

    e7ce419cf2d6ad34d01da2ceb8829eed.png

    微信 赏一包辣条吧~

    023a57327877fb4402bcc76911ec18ea.png

    支付宝 赏一听可乐吧~

    展开全文
  • 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。表数据:IDValue1tiny,small,big2small,medium3tiny,big期望得到结果:IDValue1tiny1small1big2small2medium3tiny3big正文...

    前言:

    由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。

    这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。

    表数据:

    ID

    Value

    1

    tiny,small,big

    2

    small,medium

    3

    tiny,big

    期望得到结果:

    ID

    Value

    1

    tiny

    1

    small

    1

    big

    2

    small

    2

    medium

    3

    tiny

    3

    big

    正文:

    #需要处理的表

    create table tbl_name (ID int ,mSize varchar(100));

    insert into tbl_name values (1,'tiny,small,big');

    insert into tbl_name values (2,'small,medium');

    insert into tbl_name values (3,'tiny,big');

    #用于循环的自增表

    create table incre_table (AutoIncreID int);

    insert into incre_table values (1);

    insert into incre_table values (2);

    insert into incre_table values (3);

    select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)

    from

    tbl_name a

    join

    incre_table b

    on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)

    order by a.ID;

    原理分析:

    这个join最基本原理是笛卡尔积。通过这个方式来实现循环。

    以下是具体问题分析:

    length(a.Size) - length(replace(a.mSize,',',''))+1  表示了,按照逗号分割后,改列拥有的数值数量,下面简称n

    join过程的伪代码:

    根据ID进行循环

    {

    判断:i 是否 <= n

    {

    获取最靠近第 i 个逗号之前的数据, 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)

    i = i +1

    }

    ID = ID +1

    }

    总结:

    这种方法的缺点在于,我们需要一个拥有连续数列的独立表(这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。

    例如有一行的mSize 有100个逗号分割的值,那么我们的incre_table 就需要有至少100个连续行。

    当然,mysql内部也有现成的连续数列表可用。如mysql.help_topic: help_topic_id 共有504个数值,一般能满足于大部分需求了。

    改写后如下:

    select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1)

    from

    tbl_name a

    join

    mysql.help_topic b

    on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)

    order by a.ID;

    以上这篇mysql 列转行的技巧(分享)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持脚本之家。

    您可能感兴趣的文章:mysql 行转列和列转行实例详解

    mysql 列转行,合并字段的方法(必看)

    mssql 数据库表行转列,列转行终极方案

    SQL行转列和列转行代码详解

    SQL行转列、列转行的简单实现

    mysql列转行以及年月分组实例

    SQL知识点之列转行Unpivot函数

    展开全文
  • MySQL列转行函数

    2021-08-27 20:46:23
    SELECT GROUP_CONCAT( NAME ) FROM t_tag GROUP_CONCAT 以逗号进行分割
    SELECT
    	GROUP_CONCAT( NAME ) 
    FROM
    	t_tag
    

    GROUP_CONCAT 以逗号进行分割

    展开全文
  • tag40 | +------------+-------+-------+-------+-------+-------+ 我的思路: 1、把行的转换成: +------------+-------+-------+-------+----| | a | b1 | +------------+-------+-------+-------+----| | 2011-...
  • MySQL 列转行用法实现

    2019-09-30 22:48:57
    需求 需要将如下所示原始表数据转为结构化的数据按行显示: 转为结构化数据: ...如果是单条记录通过SUBSTRING_INDEX容易实现,SQL语句如下: ...Mysql group_concat的反向应用实现(Mysql列转行
  • MySQL列转行

    2021-07-25 22:24:10
    MySQL列转行 列:数据库中存在如下的数据 教师ID 星期 是否有课 1 1 1 1 2 1 2 1 1 3 4 1 4 1 1 统计出每个老师每个工作日的上课数,要求结构如下 教师ID 星期一 星期二 星期三 星期四 1 ...
  • file,@fild, t2.num_rn + 1), @fild, -1) as result --列转行 FROM(SELECT 1 asid ,'[{"val":4,"area":"floor","setVal":null,"isExceed":false,"isQualified":true},{"val":11,"area":"floor","setVal":null,...
  • 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。表数据:IDValue1tiny,small,big2small,medium3tiny,big期望得到结果:IDValue1tiny1small1big2small2medium3tiny3big正文...
  • KEY `form_group_id` (`form_group_id`) ) 1、初始效果 2、行转效果 3、最终合并效果 sql实现 select #合并 GROUP_CONCAT(changeContext SEPARATOR '') changeContext, GROUP_CONCAT(changeBefore SEPARATOR...
  • # 列转行 表有字段a,b,c,d;要求 写一条sql语句可以根据a=1查出的效果: a_1 b_2 c_2 d_4 ![图片说明](https://img-ask.csdn.net/upload/201911/14/1573719981_394391.jpg)
  • MySQL列转行统计数据

    千次阅读 2017-06-30 14:37:47
    mysql 提供了sum求和函数。但是没有提供相乘的函数。 但还是有办法做的。笔者这里采用了列传行的办法进行统计数据。 sql如上 SELECT target.workDays * target.workFee * target.workDays FROM ( SELECT ...
  • MySQL中,如何将转成一行?比如一个一个商品会属于多个分类(如华为手机可以是手机分类,也可以是数码分类),如何将此商品在一条数据中展现所有分类。 思路很简单,通过MySQL函数group_concat即可解决。 创建测试...
  • Mysql列转行且排序

    2019-09-23 17:53:47
    SELECT strategy.strategy_id, strategy.strategy_name, strategy.used_num, strategy.material_id, strate...
  • MySql 列转行实例

    2016-06-03 11:25:55
    Mysql中使用存储过程,动态的把需要转换为行。一个小小的例子,非常不错,有需要的可以下载。
  • 查询数据 select * from user2 思路 把arms的数据单独查询出来,并自己添加一equipment,使其数据全部等于’arms‘。 select user_name,'arms' as equipment,arms from user2 把clothing的数据单独查询出来,...
  • mySQL列转行

    千次阅读 2019-06-20 15:09:42
    SELECT GROUP_CONCAT(city_id SEPARATOR ',') AS union_id FROM sys_cities;
  • Mysql 列转行

    2018-12-21 17:33:51
    列转行 原表 结果表 sql语句: select max(case when subject='Math' then score else 0 end) as Math, max(case when subject='Chinese' then score else 0 end) as Chinese, max(case when subject='...
  • MySqL 列转行 行转列

    2019-08-01 15:04:37
    一:列转行 现在有一个用户属性表,它包含以下字段: 主键ID(id),用户属性(key),用户属性值(val),用户ID(owner) 原始数据如下图所示: 需求:要求将上图中同一个用户的属性保存在一条记录中,如下图: ...
  • mysql 列转行 动态查询列

    千次阅读 2019-09-10 17:59:17
    参考链接https://cloud.tencent.com/developer/ask/223926 建表语句 create table tb_score( id int(11) not null auto_increment, userid varchar(20) not null comment '用户id', ... subject varchar(20) ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,844
精华内容 1,537
关键字:

mysql列转行

mysql 订阅
友情链接: cp.zip